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
Source: India - RG1 Register Report (XML) - Not Supported: Reserved For Future Use
Short Name: JAIFRMRG1_XML
DB package: JA_JAINRG1_XMLP_PKG
Run
JA India - RG1 Register - draft and other Oracle EBS reports with Blitz Report™ on our demo environment
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 | |
|---|---|---|---|
| Organization |
|
LOV Oracle | |
| Location |
|
LOV Oracle | |
| Transaction From Date |
|
Date | |
| Transaction To Date |
|
Date | |
| Show All Items Stock |
|
LOV Oracle |
