JA India - RG1 Register - draft

Description
Categories: BI Publisher
Application: Asia/Pacific Localizations
Source: India - RG1 Register Report (XML) - Not Supported: Reserved For Future Use
Short Name: JAIFRMRG1_XML
DB package: JA_JAINRG1_XMLP_PKG
		 SELECT 
	'1'				Query_no,
	a.register_id			register_id,
	a.slno				slno,
	a.inventory_item_id			inventory_item_id,
                trunc(a.creation_date)                             transaction_date,  
	c.description			Location_Name,
	A.transaction_type			transaction_type,
	D.concatenated_segments || ' --> ' || substr(D.description,1,50)  Item_name,
	b.name 				name, --Reports CleanUp
	c.location_code			location_code,
	c.address_line_1			address_line_1,
	c.address_line_2			address_line_2,
	c.address_line_3			address_line_3,
	e.EXCISE_DUTY_RANGE		EXCISE_DUTY_RANGE,
	e.EXCISE_DUTY_DIVISION		EXCISE_DUTY_DIVISION,
	e.EXCISE_DUTY_CIRCLE		EXCISE_DUTY_CIRCLE,
	e.EXCISE_DUTY_COMM		EXCISE_DUTY_COMM,
	e.EC_CODE			EC_CODE,
	a.manufactured_qty			manufactured_qty,
	a.manufactured_packed_qty		manufactured_packed_qty,
	a.manufactured_loose_qty		manufactured_loose_qty,
	for_home_use_pay_ed_qty		homeqty,
	for_home_use_pay_ed_val		homeval,
 	a.for_export_pay_ed_qty		exptqty,
	for_export_pay_ed_val		exptval,
	for_export_n_pay_ed_qty		exptqty2,
	to_other_factory_n_pay_ed_qty	otherfactqty,
	other_purpose			otherpur,
	decode(a.issue_type,  'OPWE', other_purpose_pay_ed_qty, other_purpose_n_pay_ed_qty)  otherpurqty,  --Nagaraj.s for Bug2676756
	a.fin_year				fin_year,
	excise_duty_rate			erate,
	decode(a.issue_type, 
		'OPWE', NVL(a.basic_ed, 0) + NVL(a.additional_ed, 0) + NVL(a.other_ed, 0),
		'EWE' , NVL(a.basic_ed, 0) + NVL(a.additional_ed, 0) + NVL(a.other_ed, 0),
		'HU',  NVL(a.basic_ed, 0) + NVL(a.additional_ed, 0) + NVL(a.other_ed, 0), a.excise_duty_amount ) eamount,
	remarks				remarks,
	balance_packed			balance_packed,
	balance_loose			balance_loose,
	(nvl(Balance_packed,0) + nvl(balance_loose,0)) closebal,
	d.primary_unit_of_measure		uom,
	jain_mtl.item_tariff			item_tariff,
	jain_mtl.item_folio			item_folio,
	a.payment_register				payment_register,
	a.register_id_part_ii			register_id_part_ii
              , a.cess_amt    
              , a.sh_cess_amt  
              , a.source,
	a.ref_doc_no ,
	JA_JAINRG1_XMLP_PKG.cf_1formula0109(a.inventory_item_id, a.slno) CF_Folio, 
	JA_JAINRG1_XMLP_PKG.cf_eamountformula(A.transaction_type, decode ( a.issue_type , 'OPWE' , NVL ( a.basic_ed , 0 ) + NVL ( a.additional_ed , 0 ) + NVL ( a.other_ed , 0 ) , 'EWE' , NVL ( a.basic_ed , 0 ) + NVL ( a.additional_ed , 0 ) + NVL ( a.other_ed , 0 ) , 'HU' , NVL ( a.basic_ed , 0 ) + NVL ( a.additional_ed , 0 ) + NVL ( a.other_ed , 0 ) , a.excise_duty_amount )) CF_eamount, 
	JA_JAINRG1_XMLP_PKG.cf_open_balformula() CF_open_bal, 
	JA_JAINRG1_XMLP_PKG.cf_prev_balformula(JA_JAINRG1_XMLP_PKG.cf_tot_cr_balformula(A.transaction_type, JA_JAINRG1_XMLP_PKG.cf_1formula(a.fin_year, a.inventory_item_id, a.slno, '1', balance_packed), a.manufactured_packed_qty), JA_JAINRG1_XMLP_PKG.cf_1formula0035(A.transaction_type, JA_JAINRG1_XMLP_PKG.cf_openbal_packedformula(a.fin_year, a.inventory_item_id, a.slno, '1', balance_loose), a.manufactured_loose_qty) , for_home_use_pay_ed_qty, a.for_export_pay_ed_qty, to_other_factory_n_pay_ed_qty, decode ( a.issue_type , 'OPWE' , other_purpose_pay_ed_qty , other_purpose_n_pay_ed_qty )) CF_Prev_Bal, 
	JA_JAINRG1_XMLP_PKG.cf_tot_cr_balformula(A.transaction_type, JA_JAINRG1_XMLP_PKG.cf_1formula(a.fin_year, a.inventory_item_id, a.slno, '1', balance_packed), a.manufactured_packed_qty) CF_tot_cr_bal_packed, 
	JA_JAINRG1_XMLP_PKG.cf_1formula0035(A.transaction_type, JA_JAINRG1_XMLP_PKG.cf_openbal_packedformula(a.fin_year, a.inventory_item_id, a.slno, '1', balance_loose), a.manufactured_loose_qty) CF_tot_cr_bal_loose, 
	JA_JAINRG1_XMLP_PKG.cf_remarksformula(a.register_id_part_ii, a.payment_register, remarks) CF_remarks, 
	JA_JAINRG1_XMLP_PKG.cf_2formula(a.manufactured_qty, for_home_use_pay_ed_qty, for_export_n_pay_ed_qty, a.for_export_pay_ed_qty, to_other_factory_n_pay_ed_qty, decode ( a.issue_type , 'OPWE' , other_purpose_pay_ed_qty , other_purpose_n_pay_ed_qty )) CF_calc, 
	JA_JAINRG1_XMLP_PKG.cf_1formula(a.fin_year, a.inventory_item_id, a.slno, '1', balance_packed) CF_openbal_packed, 
	JA_JAINRG1_XMLP_PKG.cf_openbal_packedformula(a.fin_year, a.inventory_item_id, a.slno, '1', balance_loose) CF_openbal_loose, 
	JA_JAINRG1_XMLP_PKG.cf_opensecondformula(:CS_calc) CF_opensecond, 
	JA_JAINRG1_XMLP_PKG.cf_sh_cess_amtformula(a.payment_register, a.register_id_part_ii, a.register_id, a.ref_doc_no, a.source, a.sh_cess_amt, A.transaction_type) CF_SH_CESS_AMT, 
	JA_JAINRG1_XMLP_PKG.cf_cess_amtformula(a.payment_register, a.register_id_part_ii, a.ref_doc_no, a.source, a.cess_amt, A.transaction_type) CF_CESS_AMT,
	JA_JAINRG1_XMLP_PKG.CP_cess_rate_p CP_cess_rate,
	JA_JAINRG1_XMLP_PKG.CP_SH_CESS_RATE_p CP_SH_CESS_RATE
FROM
    JAI_CMN_RG_I_TRXS a,
    hr_all_organization_units b,
    hr_locations c,
    mtl_system_items_kfv d,
    JAI_CMN_INVENTORY_ORGS e,
    JAI_INV_ITM_SETUPS jain_mtl
WHERE a.organization_id = :p_organization_id
AND a.location_id = :p_location_id
&p_query_concat
AND b.organization_id = a.organization_id
AND c.location_id = a.location_id
AND c.inventory_organization_id  = a.organization_id
AND d.inventory_item_id(+) = a.inventory_item_id
AND d.organization_id(+) = a.organization_id
AND e.organization_id = a.organization_id
AND e.location_id = a.location_id
AND jain_mtl.inventory_item_id = a.inventory_item_id
AND jain_mtl.organization_id = a.organization_id
UNION
SELECT
	'2'				Query_no,
	TO_NUMBER(NULL)		register_id,  
	TO_NUMBER(NULL)		slno, 
	a.inventory_item_id			inventory_item_id, 
	nvl(:p_trn_to_date,TO_DATE(sysdate,'DD-MON-YY'))	transaction_date, 
	C.description			Location_Name, 
	TO_CHAR(NULL)			transaction_type, 
	D.concatenated_segments || ' --> ' || substr(D.description,1,50)  Item_name,
	B.organization_name		name,      
	C.location_code			location_code,
	C.address_line_1			address_line_1,
	C.address_line_2			address_line_2,
	C.address_line_3			address_line_3,
	E.EXCISE_DUTY_RANGE		EXCISE_DUTY_RANGE,      
	E.EXCISE_DUTY_DIVISION		EXCISE_DUTY_DIVISION,   
	E.EXCISE_DUTY_CIRCLE		EXCISE_DUTY_CIRCLE,     
	E.EXCISE_DUTY_COMM		EXCISE_DUTY_COMM,       
	E.EC_CODE			EC_CODE,
	TO_NUMBER(NULL)		manufactured_qty, 
	TO_NUMBER(NULL)		manufactured_packed_qty, 
	TO_NUMBER(NULL)		manufactured_loose_qty,
	TO_NUMBER(NULL)		homeqty, 
	TO_NUMBER(NULL)		homeval, 
	TO_NUMBER(NULL)		exptqty,
	TO_NUMBER(NULL)		exptval, 
	TO_NUMBER(NULL)		exptqty2, 
	TO_NUMBER(NULL)		otherfactqty, 
	TO_CHAR(NULL)			otherpur, 
	TO_NUMBER(NULL)		otherpurqty, 
	TO_NUMBER(NULL)		fin_year,
	TO_NUMBER(NULL)		erate, 
	TO_NUMBER(NULL)		eamount,
	TO_CHAR(NULL)			remarks,
	balance_packed		balance_packed,
	balance_loose		balance_loose,
	(Balance_packed + balance_loose)	closebal,
	d.primary_unit_of_measure		uom,
	jain_mtl.item_tariff			item_tariff, 
	jain_mtl.item_folio			item_folio,
	TO_CHAR(NULL)		payment_register,
	TO_NUMBER(NULL)		register_id_part_ii,
                to_number(NULL)                                    cess_amt,
	to_number(NULL)                                    sh_cess_amt, 
                 to_char(NULL)                                         source, 
	a.ref_doc_no 		
	,
JA_JAINRG1_XMLP_PKG.cf_1formula0109(a.inventory_item_id, TO_NUMBER(NULL)) CF_Folio, 
	JA_JAINRG1_XMLP_PKG.cf_eamountformula(TO_CHAR(NULL),TO_NUMBER(NULL)) CF_eamount, 
	JA_JAINRG1_XMLP_PKG.cf_open_balformula() CF_open_bal, 
	JA_JAINRG1_XMLP_PKG.cf_prev_balformula(JA_JAINRG1_XMLP_PKG.cf_tot_cr_balformula(TO_CHAR(NULL), JA_JAINRG1_XMLP_PKG.cf_1formula(TO_NUMBER(NULL), a.inventory_item_id, TO_NUMBER(NULL), '2', balance_packed), TO_NUMBER(NULL)), JA_JAINRG1_XMLP_PKG.cf_1formula0035(TO_CHAR(NULL), JA_JAINRG1_XMLP_PKG.cf_openbal_packedformula(TO_NUMBER(NULL), a.inventory_item_id, TO_NUMBER(NULL), '2', balance_loose), TO_NUMBER(NULL)), TO_NUMBER(NULL), TO_NUMBER(NULL), TO_NUMBER(NULL),TO_NUMBER(NULL)) CF_Prev_Bal,	
	JA_JAINRG1_XMLP_PKG.cf_tot_cr_balformula(TO_CHAR(NULL), JA_JAINRG1_XMLP_PKG.cf_1formula(TO_NUMBER(NULL), a.inventory_item_id, TO_NUMBER(NULL), '2', balance_packed), TO_NUMBER(NULL)) CF_tot_cr_bal_packed, 
	JA_JAINRG1_XMLP_PKG.cf_1formula0035(TO_CHAR(NULL), JA_JAINRG1_XMLP_PKG.cf_openbal_packedformula(TO_NUMBER(NULL), a.inventory_item_id, TO_NUMBER(NULL), '2', balance_loose), TO_NUMBER(NULL)) CF_tot_cr_bal_loose, 
	JA_JAINRG1_XMLP_PKG.cf_remarksformula(TO_NUMBER(NULL), TO_CHAR(NULL), TO_CHAR(NULL)) CF_remarks, 
	JA_JAINRG1_XMLP_PKG.cf_2formula(TO_NUMBER(NULL), TO_NUMBER(NULL), TO_NUMBER(NULL), TO_NUMBER(NULL), TO_NUMBER(NULL),TO_NUMBER(NULL)) CF_calc, 
	JA_JAINRG1_XMLP_PKG.cf_1formula(TO_NUMBER(NULL), a.inventory_item_id, TO_NUMBER(NULL), '2', balance_packed) CF_openbal_packed, 
	JA_JAINRG1_XMLP_PKG.cf_openbal_packedformula(TO_NUMBER(NULL), a.inventory_item_id, TO_NUMBER(NULL), '2', balance_loose) CF_openbal_loose, 
	JA_JAINRG1_XMLP_PKG.cf_opensecondformula(:CS_calc) CF_opensecond, 
	JA_JAINRG1_XMLP_PKG.cf_sh_cess_amtformula(TO_CHAR(NULL),TO_NUMBER(NULL), TO_NUMBER(NULL), a.ref_doc_no, to_char(NULL), TO_NUMBER(NULL),TO_CHAR(NULL)) CF_SH_CESS_AMT, 
	JA_JAINRG1_XMLP_PKG.cf_cess_amtformula(TO_CHAR(NULL), TO_NUMBER(NULL), a.ref_doc_no,to_char(NULL), TO_NUMBER(NULL), TO_CHAR(NULL)) CF_CESS_AMT,
	JA_JAINRG1_XMLP_PKG.CP_cess_rate_p CP_cess_rate,
	JA_JAINRG1_XMLP_PKG.CP_SH_CESS_RATE_p CP_SH_CESS_RATE
FROM JAI_CMN_RG_I_TRXS A,
	org_organization_definitions B, 
	hr_locations C,
	mtl_system_items_kfv D,
	JAI_CMN_INVENTORY_ORGS E,
	JAI_INV_ITM_SETUPS JAIN_MTL 
WHERE :P_SHOW_ALL_ITEMS = 'Y'
AND A.REGISTER_ID IN (
	SELECT REGISTER_ID FROM 
	(
	 SELECT RG.ORGANIZATION_ID, RG.LOCATION_ID, RG.INVENTORY_ITEM_ID, MAX(RG.REGISTER_ID) REGISTER_ID 
	 FROM   JAI_CMN_RG_I_TRXS RG
	 WHERE  
	 TO_DATE(RG.creation_date,'DD-MON-YY') < NVL(:P_TRN_FROM_DATE,TO_DATE(RG.creation_DATE,'DD-MON-YY'))
	 AND
	 (RG.ORGANIZATION_ID, RG.LOCATION_ID,RG.INVENTORY_ITEM_ID) NOT IN 
	  (
	   SELECT ORGANIZATION_ID, LOCATION_ID, INVENTORY_ITEM_ID 
	   FROM   JAI_CMN_RG_I_TRXS a
	   WHERE a.register_id =  a.register_id &p_query_concat
	  )  
	 GROUP BY RG.ORGANIZATION_ID, RG.LOCATION_ID, RG.INVENTORY_ITEM_ID
	)
   )
AND  NVL(BALANCE_PACKED,0) + NVL(BALANCE_LOOSE,0)>0
AND  A.organization_id             = :p_organization_id
and    A.inventory_item_id           = nvl(:p_inventory_item_id, A.inventory_item_id)
and    A.location_id                 = :p_location_id                                               
and    B.organization_id             = A.organization_id
and    C.location_id                 = A.location_id
and    C.inventory_organization_id   = A.organization_id
and    D.inventory_item_id(+)        = A.inventory_item_id
and    D.organization_id(+)          = A.organization_id
and    e.organization_id             = a.organization_id
and    e.location_id		     = a.location_id
AND    jain_mtl.inventory_item_id =  a.inventory_item_id
AND    jain_mtl.organization_id =   a.organization_id
--ORDER BY item_tariff, item_name, register_id
ORDER BY 38 ASC,8 ASC,10 ASC,9 ASC,6 ASC,11 ASC,12 ASC,13 ASC,14 ASC,15 ASC,16 ASC,17 ASC,4 ASC,18 ASC,30 ASC,1 ASC,37 ASC , item_tariff , item_name , register_id
Parameter Name SQL text Validation
Show All Items Stock
 
LOV Oracle
Transaction To Date
 
Date
Transaction From Date
 
Date
Location
 
LOV Oracle
Organization
 
LOV Oracle