ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN 82288223F0D13869E053B46B63588994Y SELECT b.*, (nvl(b.opening_balance_qty, 0) + b.bal_qty) balance_qty, c.tax_type_name, c.tax_amount, c.type_num, c.row_num FROM (SELECT a.*, ROWNUM row_n, decode(a.trx_flag, 'I', -1, 'R', 1, 1) * a.quantity qty, SUM(decode(a.trx_flag, 'I', -1, 'R', 1, 1) * a.quantity) OVER(PARTITION BY a.itme_id, a.organization_id ORDER BY ROWNUM, a.trx_date) bal_qty FROM (SELECT c.rep_context_entity_name operating_unit, d.first_party_primary_reg_num tax_registration_num, c.rep_organization_id organization_id, c.rep_organization_name organization_name, c.rep_location_id location_id, c.rep_location_name location_name, d.product_description item_description, d.product_id itme_id, NULL item_tariff_no, NULL item_folio_no, d.product_description item_desc, CASE WHEN d.applied_from_entity_code = 'BILL_OF_ENTRY' THEN d.tax_invoice_number || ' ' || d.APPLIED_FROM_TRX_ID || ' ' || d.tax_invoice_date ELSE d.tax_invoice_number || ' ' || d.tax_invoice_date END manu_invinfo, d.party_name || ' ' || d.party_address party_info, --edit by hongjun.wu for bug#19528742 begin-- /*SUM(nvl(d.trx_line_quantity, 0)) quantity,*/ nvl(d.trx_line_quantity, 0) quantity, --edit by hongjun.wu for bug#19528742 end-- d.trx_number, d.TRX_TYPE, d.trx_id, trunc(d.trx_date) trx_date, d.entity_code entity_code, nvl(e.numeric4, 0) opening_balance_qty, nvl(e.numeric1, 0) qty_remaining, nvl(e.numeric2, 0) qty_matched, e.numeric3 sl_no, decode(d.entity_code, 'SALES_ORDER_ISSUE', 'I', 'OE_ORDER_HEADERS', 'I', 'RCV_TRANSACTION', decode(d.TRX_TYPE, 'RETURN TO VENDOR', 'I', 'R'), 'R') trx_flag FROM jai_rep_context_t c, jai_rep_trx_detail_t d, jai_rep_trx_jx_ext_t e WHERE c.rep_context_id = d.rep_context_id AND c.request_id = d.request_id AND d.request_id = e.request_id(+) AND d.detail_tax_line_id = e.detail_tax_line_id(+) AND c.request_id = :p_conc_request_id GROUP BY c.rep_context_entity_name, d.first_party_primary_reg_num, c.rep_organization_id, c.rep_organization_name, c.rep_location_id, c.rep_location_name, d.product_description, d.product_id, d.product_description, d.applied_from_entity_code, d.tax_invoice_number, d.APPLIED_FROM_TRX_ID, d.tax_invoice_date, d.party_name, d.party_address, nvl(d.trx_line_quantity, 0), --add by hongjun.wu for bug#19528742 d.trx_number, d.trx_type, d.trx_id, trunc(d.trx_date), d.entity_code, e.numeric4, e.numeric1, e.numeric2, e.numeric3 ORDER BY /*e.numeric3*/trunc(d.trx_date)/*edit by hongjun.wu for bug#19528742*/) a) b, (SELECT d.tax_type_name tax_type_name, d.trx_id trx_id, d.entity_code entity_code, d.trx_type trx_type, COUNT(d.tax_type_name) OVER(PARTITION BY d.trx_id, d.entity_code, d.trx_type ORDER BY d.trx_id) type_num, ROW_NUMBER() OVER(PARTITION BY d.trx_id, d.entity_code, d.trx_type ORDER BY d.trx_id) row_num, SUM(nvl(d.tax_amt_funcl_curr, 0)) tax_amount FROM jai_rep_trx_detail_t d WHERE d.request_id = :p_conc_request_id GROUP BY d.tax_type_name, d.trx_id, d.entity_code, d.trx_type) c WHERE c.trx_id = b.trx_id AND c.entity_code = b.entity_code AND nvl(c.trx_type, '#') = nvl(c.TRX_TYPE, nvl(b.trx_type, '#')) AND b.trx_date BETWEEN nvl(:p_trx_date_from, b.trx_date) AND nvl(:p_trx_date_to, b.trx_date) --add by hongjun.wu for bug#19528742 ORDER BY b.organization_id, b.itme_id, /*b.trx_id, comment by hongjun.wu for bug#19528742*/ b.row_n, b.sl_no, b.trx_date, c.row_num JA JAINRG23_XML US JA India - RG23D Register Report(XML Publisher) Application: Asia/Pacific Localizations Source: India - RG23D Register Report(XML Publisher) Short Name: JAINRG23_XML DB package: JAI_EXTRACT_PKG ZHS JA 印度 - RG23D 登记报表 (XML Publisher) Application: 亚太地区本地化 Source: 印度 - RG23D 登记报表 (XML Publisher) Short Name: JAINRG23_XML DB package: JAI_EXTRACT_PKG BI Publisher :p_accounting_status :p_all_item_stock :p_ar_exemption_status :p_balancing_segment_high :p_balancing_segment_low :p_conc_request_id :p_currency_code_high :p_currency_code_low :p_document_type :p_exemption_flag :p_exemption_number :p_exemption_type :p_form_tracking_flag :p_form_type :p_gl_date_high :p_gl_date_low :p_gl_period_name_high :p_gl_period_name_low :p_inter_state_transaction :p_item_class :p_item_id :p_item_only_flag :p_ledger_id :p_legal_entity_id :p_location_id :p_match_status :p_mtl_tran_check_flag :p_org_id :p_organization_id :p_par_registration_status :p_party_id :p_party_name :p_party_site_id :p_party_type :p_posting_status :p_product :p_proof_received_status :p_reg_reporting_context :p_register_type :p_report_name :p_reporting_context :p_reporting_level :p_repository_flag :p_rept_level :p_statistics_beginning_flag :p_statistics_so_flag :p_summary_block_type :p_summary_level :p_suppl_invoice_type_code :p_suppl_process_date_from :p_suppl_process_date_to :p_suppl_show_unraised_flag :p_suppl_trx_date_from :p_suppl_trx_date_to :p_suppl_trx_number :p_tax_account_high :p_tax_account_low :p_tax_category_id :p_tax_invoice_date_high :p_tax_invoice_date_low :p_tax_rate_code_high :p_tax_rate_code_low :p_tax_rate_id :p_tax_reg_num :p_tax_regime_code :p_tax_regime_id :p_tax_status_code :p_tax_type_code_high :p_tax_type_code_low :p_tax_type_id :p_third_party_rep_level :p_trading_flag :p_trx_date_from :p_trx_date_period_name_high :p_trx_date_period_name_low :p_trx_date_to :p_trx_id :p_trx_number_high :p_trx_number_low :p_use_parallel_hint 1 10 :p_org_id LOV Oracle JA_IN_REP_ORG_ID Y select hou.organization_id id, hou.name value, null description from hr_operating_units hou where set_of_books_id=fnd_profile.value('GL_SET_OF_BKS_ID') order by value,description Y US Operating Unit ZHS 业务实体 2 20 :p_tax_regime_id LOV Oracle JA_IN_REP_REGIME_TRAD Y select regime_id id, regime_name value, null description from jai_regimes where regime_id in ( select reg_line.regime_id from jai_party_regs_v reg,jai_party_reg_lines_v reg_line where reg.party_reg_id=reg_line.party_reg_id and reg.reg_class_code='FIRST_PARTY' and org_classification_code='TRADING' ) order by value,description Y US Tax Regime ZHS 税收制度 3 30 :p_tax_reg_num LOV Oracle JA_IN_REP_REG_NUM_TRAD Y select reg_line.registration_number id, reg_line.registration_number value, null description from jai_party_regs_v reg,jai_party_reg_lines_v reg_line where reg.party_reg_id=reg_line.party_reg_id and reg.reg_class_code='FIRST_PARTY' and reg.org_classification_code='TRADING' and reg_line.regime_id=:$flex$.ja_in_rep_regime_trad order by reg_line.registration_number Y US Tax Registration Number ZHS 纳税登记编号 4 40 :p_organization_id LOV Oracle JA_IN_REP_ORGANIZATION_TRAD Y select organization_id id, name value, null description from hr_all_organization_units where exists (select 'X' from jai_party_regs_v reg,jai_party_reg_lines_v reg_line where reg.reg_class_code='FIRST_PARTY' and reg.party_id=organization_id and reg_line.party_reg_id=reg.party_reg_id and reg_line.registration_number=:$flex$.ja_in_rep_reg_num_trad) order by value,description SELECT NAME, ORGANIZATION_ID FROM HR_ALL_ORGANIZATION_UNITS WHERE EXISTS (SELECT 'X' FROM jai_party_regs_v reg, jai_party_reg_lines_v reg_line WHERE reg.REG_CLASS_CODE = 'FIRST_PARTY' AND reg.party_id = organization_id AND reg_line.party_reg_id = reg.party_reg_id AND reg_line.registration_number = :$FLEX$.Tax_Registration_Number) US Inventory Organization ZHS 库存组织 5 50 :p_location_id LOV Oracle JA_IN_REP_LOCATION_TRAD Y select hla.location_id id, hla.location_code value, null description from hr_locations_all hla where exists (select 'X' from jai_party_regs reg where reg.party_id=:$flex$.ja_in_rep_organization_trad and reg.org_classification_code='TRADING' and reg.party_site_id=hla.location_id) order by value,description SELECT hla.location_code, hla.LOCATION_ID FROM HR_LOCATIONS_ALL hla WHERE EXISTS (SELECT 'X' FROM jai_party_regs reg WHERE reg.party_id = :$FLEX$.Inventory_Organization AND reg.party_site_id = hla.location_id) US Location ZHS 地点 6 60 :p_trx_date_from Date US Date From ZHS 日期自 7 70 :p_trx_date_to Date US Date To ZHS 日期至 8 80 :p_item_id LOV Oracle JA_IN_REP_INV_ITEM_TRAD Y select msik.inventory_item_id id, msik.concatenated_segments value, null description from mtl_system_items_kfv msik where msik.organization_id=:$flex$.ja_in_rep_organization_trad order by concatenated_segments US Inventory Item ZHS 库存项目