ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN 82288223F0A43869E053B46B63588994 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 Y JA JAIFRMRG1_XML US JA India - RG1 Register - draft 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 ZHS JA 印度 - RG1 登记报表- 不支持：已保留供将来使用 Application: 亚太地区本地化 Source: 印度 - RG1 登记报表 (XML) - 不支持：已保留供将来使用 Short Name: JAIFRMRG1_XML DB package: JA_JAINRG1_XMLP_PKG BI Publisher &p_query_concat :cp_1 :cp_cess_rate :cp_sh_cess_rate :cs_calc :last_page :p_conc_request_id :p_first_rec :p_fiscal_year :p_inventory_item_id :p_location_id :p_month :p_open :p_open_bal :p_organization_id :p_prev_close_bal :p_query_concat :p_show_all_items :p_start_page_no :p_trn_from_date :p_trn_to_date :prev_page :validation_flag 1 10 :p_organization_id LOV Oracle JA_IN_MFG_ORGANIZATION_NAME Y select hru.organization_id id, hru.name value, null description from hr_all_organization_units hru where exists ( select '1' from jai_cmn_inventory_orgs jhru where jhru.organization_id=hru.organization_id and nvl(jhru.manufacturing,'N')='Y' ) order by hru.name Y US Organization ZHS 组织 2 20 :p_location_id LOV Oracle JA_IN_MFG_LOCATION_NAME Y select hrl.location_id id, hrl.description value, null description from hr_locations hrl where exists ( select '1' from jai_cmn_inventory_orgs jhru where jhru.organization_id=:$flex$.ja_in_mfg_organization_name and jhru.location_id=hrl.location_id and nvl(jhru.manufacturing,'N')='Y' ) order by hrl.description Y US Location ZHS 地点 3 30 :p_trn_from_date Date US Transaction From Date ZHS 事务处理起始日期 4 40 :p_trn_to_date Date US Transaction To Date ZHS 事务处理终止日期 5 50 :p_show_all_items LOV Oracle JAI_Yes_No Y select lookup_code id, meaning value, null description from fnd_lookups where fnd_lookups.lookup_type='YES_NO' order by value,description N US Show All Items Stock ZHS 显示所有项目库存