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 |
