ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN D49619EA723A4EBAE0530100007FD3B2Y select /*+ push_pred(wfc) push_pred(shipped_lots) push_pred(picked_lots) */ haou2.name selling_operating_unit, haou1.name shipping_operating_unit, ood.organization_code, -- delivery level info wnd.name delivery_note_number, trunc(nvl(mmt.transaction_date,wnd.initial_pickup_date)) delivery_note_date, xxen_util.meaning(wnd.status_code,'DELIVERY_STATUS',665) delivery_status, trunc(mmt.transaction_date) actual_shipment_date, trunc(wnd.confirm_date) confirmed_date, wnd.confirmed_by, -- delivery detail info xxen_util.meaning(wdd.released_status,'PICK_STATUS',665) release_status, trunc(wpb.creation_date) released_date, -- freight cost details wfc.freight_cost_currency, wfc.freight_cost_name, wfc.freight_cost_type, wfc.freight_cost, -- customer details hca.account_number customer_number, nvl(hca.account_name,hp.party_name) customer_name, hcsua.location customer_ship_to_site, hz_format_pub.format_address(hl.location_id,null,null,',') customer_ship_to_address, hl.postal_code, hl.city, hl.country, wdd.cust_po_number customer_po_number, wnd.name customer_delivery_name, -- delivery item details msiv.concatenated_segments item, wdd.item_description item_description, xxen_util.meaning(msiv.item_type,'ITEM_TYPE',3) user_item_type, &category_columns oola.ordered_item, wdd.requested_quantity, decode(wdd.released_status,'Y',nvl(wdd.picked_quantity,wdd.requested_quantity),null) picked_qty, wdd.shipped_quantity, wdd.delivered_quantity, wdd.cancelled_quantity, wdd.requested_quantity_uom uom, -- hold status (aggregated once upstream, not per-row scalar subqueries) xxen_util.yes(nvl2(line_holds.line_id,'Y',null)) line_on_hold, xxen_util.yes(nvl2(header_holds.header_id,'Y',null)) header_on_hold, -- salesperson rs_order.name salesperson_order, rs_cust.name salesperson_customer, -- packing metrics wdd.net_weight, wdd.gross_weight, wdd.weight_uom_code weight_uom, wdd.volume, wdd.volume_uom_code volume_uom, wdd.unit_price, wdd.currency_code, -- shipment details wnd.waybill, (select listagg(x.name,', ') within group (order by x.name) from (select distinct wt.name, wdl.delivery_id from wsh_delivery_legs wdl, wsh_trip_stops wts, wsh_trips wt where wdl.pick_up_stop_id=wts.stop_id and wts.trip_id=wt.trip_id) x where x.delivery_id=wnd.delivery_id) trip_number, wsh_util_core.derive_shipment_priority(wnd.delivery_id) shipment_priority, wsh_util_core.ship_method_to_freight(wnd.ship_method_code,wdd.organization_id) delivery_freight_code, xxen_util.meaning(wnd.ship_method_code,'SHIP_METHOD',3) delivery_shipping_method, xxen_util.meaning(wnd.freight_terms_code,'FREIGHT_TERMS',660) delivery_freight_terms, nvl(xxen_util.meaning(wnd.fob_code,'FOB',222),wnd.fob_code) delivery_fob, wsh_util_core.ship_method_to_freight(nvl(wdd.ship_method_code,wnd.ship_method_code),wdd.organization_id) delivery_line_freight_code, xxen_util.meaning(nvl(wdd.ship_method_code,wnd.ship_method_code),'SHIP_METHOD',3) delivery_line_shipping_method, xxen_util.meaning(nvl(wdd.freight_terms_code,wnd.freight_terms_code),'FREIGHT_TERMS',660) delivery_line_freight_terms, nvl(xxen_util.meaning(nvl(wdd.fob_code,wnd.fob_code),'FOB',222),wnd.fob_code) delivery_line_fob, wdd.tracking_number, wdd.customer_dock_code, -- container/lot details wdd.container_flag, xxen_util.meaning(wdd.container_type_code,'CONTAINER_TYPE',401) container_type, wdd.container_name, coalesce(wdd.lot_number,shipped_lots.lot_numbers,picked_lots.lot_numbers) lot_numbers, -- exceptions (select dbms_lob.substr(rtrim(xmlagg(xmlelement(name excptn,wev.description,',').extract('//text()') order by wev.description).GetClobVal(),','),4000,1) from (select distinct wev.delivery_detail_id, wev.description from wsh_exceptions_v wev where wev.status='OPEN' and wev.severity='ERROR') wev where wev.delivery_detail_id=wdd.delivery_detail_id) error_exceptions, -- source references/invoice details wpb.name release_batch, coalesce( ( select mmt.pick_slip_number from mtl_material_transactions mmt where mmt.move_order_line_id=wdd.move_order_line_id and mmt.transaction_id=decode(nvl(wdd.transaction_id,-99),-99,mmt.transaction_id,wdd.transaction_id) and mmt.transaction_source_type_id in (2,8) and nvl(mmt.transaction_quantity,0)<0 and --> to prevent duplicates, just take the -qty trx leg sub to staging. mmt.pick_slip_number is not null and wdd.source_code='OE' and wdd.released_status<>'S' and -- not yet released to warehouse rownum=1 ), ( select mtrl.pick_slip_number from mtl_txn_request_lines mtrl where mtrl.line_id=wdd.move_order_line_id and mtrl.pick_slip_number is not null and wdd.source_code='OE' and wdd.released_status<>'S' and -- not yet released to warehouse wdd.transaction_id is null and rownum=1 ), ( select mmtt.pick_slip_number from mtl_material_transactions_temp mmtt where mmtt.move_order_line_id=wdd.move_order_line_id and nvl(mmtt.parent_line_id,0)=0 and mmtt.pick_slip_number is not null and wdd.source_code='OE' and wdd.released_status='S' and -- released to warehouse rownum=1 ), ( select mmttp.pick_slip_number from mtl_material_transactions_temp mmtt, mtl_material_transactions_temp mmttp where mmttp.transaction_temp_id=mmtt.parent_line_id and mmtt.parent_line_id<>mmtt.transaction_temp_id and mmtt.move_order_line_id=wdd.move_order_line_id and nvl(mmtt.parent_line_id,0)=0 and mmttp.pick_slip_number is not null and wdd.source_code='OE' and wdd.released_status='S' and -- released to warehouse rownum=1 ), ( select mmtt.pick_slip_number from mtl_material_transactions_temp mmtt where mmtt.move_order_line_id=wdd.move_order_line_id and mmtt.parent_line_id=mmtt.transaction_temp_id and mmtt.pick_slip_number is not null and wdd.source_code='OE' and wdd.released_status='S' and -- released to warehouse rownum=1 ) ) pick_slip_number, (select mtrh.request_number from mtl_txn_request_lines mtrl, mtl_txn_request_headers mtrh where mtrl.header_id=mtrh.header_id and mtrl.line_id=wdd.move_order_line_id) move_order_number, wdi.sequence_number pack_slip_number, xxen_util.meaning(wdd.source_code,'SOURCE_SYSTEM',665) source, wdd.source_header_type_name source_document_type, wdd.source_header_number source_document_number, wdd.source_line_number source_document_line, ooha.ordered_date source_document_date, rcta.trx_number invoice_number, rctla.line_number invoice_line_number, rcta.trx_date invoice_date, -- date info trunc(wdd.date_requested) requested_date, trunc(wdd.date_scheduled) scheduled_date, trunc(oola.promise_date) promise_date, trunc(mmt.transaction_date)-trunc(oola.promise_date) shipment_delay, trunc(wdd.earliest_pickup_date) earliest_pickup_date, trunc(wdd.latest_pickup_date) latest_pickup_date, trunc(wdd.earliest_dropoff_date) earliest_dropoff_date, trunc(wdd.latest_dropoff_date) latest_dropoff_date, trunc(wnd.initial_pickup_date) delivery_initial_pickup_date, trunc(wnd.ultimate_dropoff_date) delivery_ultimate_dropoff_date, -- ship from/to locations wsh_util_core.get_location_description(nvl(wnd.initial_pickup_location_id,wdd.ship_from_location_id), case :loc_format when 'City State Zip' then 'CSZ' when 'City State Zip Country' then 'CSZC' when 'Code Address1 City' then 'NEW UI CODE INFO' else 'NEW UI CODE' end) ship_from_location, wsh_util_core.get_location_description(nvl(wnd.ultimate_dropoff_location_id,wdd.ship_to_location_id), case :loc_format when 'City State Zip' then 'CSZ' when 'City State Zip Country' then 'CSZC' when 'Code Address1 City' then 'NEW UI CODE INFO' else 'NEW UI CODE' end) ship_to_location, wsh_util_core.get_location_description(wdd.deliver_to_location_id, case :loc_format when 'City State Zip' then 'CSZ' when 'City State Zip Country' then 'CSZC' when 'Code Address1 City' then 'NEW UI CODE INFO' else 'NEW UI CODE' end) deliver_to_location, wsh_util_core.get_location_description(nvl(wnd.intmed_ship_to_location_id,wdd.intmed_ship_to_location_id), case :loc_format when 'City State Zip' then 'CSZ' when 'City State Zip Country' then 'CSZC' when 'Code Address1 City' then 'NEW UI CODE INFO' else 'NEW UI CODE' end) int_med_ship_to_location, wsh_util_core.get_location_description(wnd.fob_location_id, case :loc_format when 'City State Zip' then 'CSZ' when 'City State Zip Country' then 'CSZC' when 'Code Address1 City' then 'NEW UI CODE INFO' else 'NEW UI CODE' end) fob_location, -- packing/shipping instructions wdd.packing_instructions, wdd.shipping_instructions &il_reg_columns &il_columns_amt &il_columns_rate &il_columns_rec &il_columns_tp -- ids (non user-facing, kept at the end) ,wnd.delivery_id delivery_note_id, wdd.delivery_detail_id from wsh_delivery_details wdd, wsh_delivery_assignments wda, wsh_new_deliveries wnd, wsh_picking_batches wpb, wsh_document_instances wdi, wsh_carriers wc, ( select distinct x.delivery_detail_id, x.freight_cost_currency, x.freight_cost, listagg(x.freight_cost_name,',') within group (order by x.freight_cost_type_code,x.freight_cost_name) over (partition by x.delivery_detail_id,x.freight_cost_currency) freight_cost_name, listagg(x.freight_cost_type,',') within group (order by x.freight_cost_type_code,x.freight_cost_name) over (partition by x.delivery_detail_id,x.freight_cost_currency) freight_cost_type from ( select wfc.delivery_detail_id, wfc.currency_code freight_cost_currency, wfct.name freight_cost_name, wfct.freight_cost_type_code, xxen_util.meaning(wfct.freight_cost_type_code,'FREIGHT_COST_TYPE',665) freight_cost_type, sum(nvl(wfc.total_amount,wfc.unit_amount*nvl(wfc.quantity,1))) over (partition by wfc.delivery_detail_id,wfc.currency_code) freight_cost, sum(lengthb(wfct.name)+1) over (partition by wfc.delivery_detail_id,wfc.currency_code order by wfct.freight_cost_type_code,wfct.name rows between unbounded preceding and current row) lengthb1, sum(lengthb(xxen_util.meaning(wfct.freight_cost_type_code,'FREIGHT_COST_TYPE',665))+1) over (partition by wfc.delivery_detail_id,wfc.currency_code order by wfct.freight_cost_type_code,wfct.name rows between unbounded preceding and current row) lengthb2 from wsh_freight_costs wfc, wsh_freight_cost_types wfct where wfc.freight_cost_type_id=wfct.freight_cost_type_id and nvl(wfc.charge_source_code,'MANUAL') in ('PRICING_ENGINE','MANUAL') ) x where x.lengthb1<=4000 and x.lengthb2<=4000 ) wfc, mtl_system_items_vl msiv, mtl_material_transactions mmt, ( select distinct x.picking_line_id, listagg(x.lot_number,',') within group (order by x.lot_number) over (partition by x.picking_line_id) lot_numbers from ( select mmt.picking_line_id, mtln.lot_number, sum(lengthb(mtln.lot_number)+1) over (partition by mmt.picking_line_id order by mtln.lot_number rows between unbounded preceding and current row) lengthb from mtl_material_transactions mmt, mtl_transaction_lot_numbers mtln where 2=2 and mtln.transaction_id=mmt.transaction_id and mmt.transaction_source_type_id in (2,8) and mmt.transaction_quantity<0 ) x where x.lengthb<=4000 ) shipped_lots, ( select distinct x.move_order_line_id, listagg(x.lot_number,',') within group (order by x.lot_number) over (partition by x.move_order_line_id) lot_numbers from ( select mmt.move_order_line_id, mtln.lot_number, sum(lengthb(mtln.lot_number)+1) over (partition by mmt.move_order_line_id order by mtln.lot_number rows between unbounded preceding and current row) lengthb from mtl_material_transactions mmt, mtl_transaction_lot_numbers mtln where 2=2 and mtln.transaction_id=mmt.transaction_id and mmt.transaction_source_type_id in (2,8) and mmt.transaction_quantity<0 ) x where x.lengthb<=4000 ) picked_lots, oe_order_headers_all ooha, oe_order_lines_all oola, (select oohoa.line_id from oe_order_holds_all oohoa where nvl(oohoa.released_flag,'N')='N' and oohoa.line_id is not null group by oohoa.line_id) line_holds, (select oohoa.header_id from oe_order_holds_all oohoa where nvl(oohoa.released_flag,'N')='N' group by oohoa.header_id) header_holds, ra_salesreps_all rs_order, ra_salesreps_all rs_cust, ra_customer_trx_lines_all rctla, ra_customer_trx_all rcta, org_organization_definitions ood, hr_all_organization_units haou1, hz_cust_accounts hca, hz_parties hp, hz_cust_site_uses_all hcsua, hz_cust_acct_sites_all hcasa, hz_party_sites hps, hz_locations hl, hr_all_organization_units haou2 &il_from where 1=1 and nvl(wdd.line_direction,'O') in ('O','IO') and wdd.delivery_detail_id=wda.delivery_detail_id(+) and wda.delivery_id=wnd.delivery_id(+) and nvl(wnd.shipment_direction,'O') in ('O','IO') and wnd.delivery_type(+)='STANDARD' and wdd.batch_id=wpb.batch_id(+) and wnd.delivery_id=wdi.entity_id(+) and wdi.entity_name(+)='WSH_NEW_DELIVERIES' and wdi.document_type(+)='PACK_TYPE' and wdd.carrier_id=wc.carrier_id(+) and wdd.organization_id=msiv.organization_id(+) and wdd.inventory_item_id=msiv.inventory_item_id(+) and wdd.delivery_detail_id=mmt.picking_line_id(+) and mmt.transaction_source_type_id(+) in (2,8) and mmt.transaction_quantity(+)<0 and wdd.delivery_detail_id=wfc.delivery_detail_id(+) and nvl2(wdd.lot_number,-1,wdd.delivery_detail_id)=shipped_lots.picking_line_id(+) and nvl2(wdd.lot_number,-1,wdd.move_order_line_id)=picked_lots.move_order_line_id(+) and decode(wdd.source_code,'OE',wdd.source_header_id)=ooha.header_id(+) and decode(wdd.source_code,'OE',wdd.source_line_id)=oola.line_id(+) and oola.line_id=line_holds.line_id(+) and ooha.header_id=header_holds.header_id(+) and ooha.org_id=rs_order.org_id(+) and nvl(ooha.salesrep_id,-3)=rs_order.salesrep_id(+) and hcsua.org_id=rs_cust.org_id(+) and nvl(hcsua.primary_salesrep_id,-3)=rs_cust.salesrep_id(+) and rctla.interface_line_context(+)='ORDER ENTRY' and decode(wdd.source_code,'OE',wdd.source_header_number)=rctla.interface_line_attribute1(+) and decode(wdd.source_code,'OE',wdd.source_header_type_name)=rctla.interface_line_attribute2(+) and decode(wdd.source_code,'OE',to_char(wdd.source_line_id))=rctla.interface_line_attribute6(+) and nvl(rctla.interface_line_attribute3,nvl(wnd.name,'??'))=nvl(wnd.name,'??') and nvl(rctla.interface_line_attribute11,'0')='0' and rctla.customer_trx_id=rcta.customer_trx_id(+) and wdd.organization_id=ood.organization_id(+) and ood.operating_unit=haou1.organization_id(+) and wdd.customer_id=hca.cust_account_id(+) and hca.party_id=hp.party_id(+) and wdd.ship_to_site_use_id=hcsua.site_use_id(+) and hcsua.cust_acct_site_id=hcasa.cust_acct_site_id(+) and hcasa.party_site_id=hps.party_site_id(+) and hps.location_id=hl.location_id(+) and hcasa.org_id=haou2.organization_id(+) and (wdd.organization_id is null or exists (select null from org_access_view oav where oav.organization_id=wdd.organization_id and oav.resp_application_id=fnd_global.resp_appl_id and oav.responsibility_id=fnd_global.resp_id)) and ( haou1.organization_id in (select mgoat.organization_id from mo_glob_org_access_tmp mgoat union select fnd_global.org_id from dual where fnd_release.major_version=11) or haou2.organization_id in (select mgoat.organization_id from mo_glob_org_access_tmp mgoat union select fnd_global.org_id from dual where fnd_release.major_version=11) or haou1.organization_id is null and haou2.organization_id is null ) &il_where order by selling_operating_unit, shipping_operating_unit, organization_code, delivery_note_date, delivery_note_id, delivery_detail_id Added India GST columns, displayed only when India Localization is installed, and corrected the Initial Pick Up Date To filter operator. (:Selling_Operating_Unit is not null or :Shipping_Operating_Unit is not null or :Organization_Code is not null) US WSH Shipping/Delivery Transactions This report provides details of Warehouse Shipping Transactions and Deliveries. To review details of deliveries only, set the Assigned to Delivery Parameter = 'Yes' To review details of shipping transaction not yet assigned to a delivery, set the Assigned to Delivery Parameter = 'No' Set the parameter to null to review all shipping transations regardless of delivery assignment status. When India Localization is installed, the following GST columns are also included: GST Registration No, GST PAN No, CGST Amount, SGST Amount, IGST Amount, CESS Amount, Custom Amount, Unclassified Tax Amount, Taxable Value, HSN Length Warning, CGST Rate, SGST Rate, IGST Rate, CESS Rate, CGST Recoverable, CGST Non-Recoverable, SGST Recoverable, SGST Non-Recoverable, IGST Recoverable, IGST Non-Recoverable, Reverse Charge Flag, Place of Supply, Ship From State, HSN SAC Code. Please specify a value for at least one of the following parameters: Selling Operating Unit, Shipping Operating Unit, Organization Code Enginatics &category_columns &il_columns_amt &il_columns_rate &il_columns_rec &il_columns_tp &il_from &il_reg_columns &il_where 1=1 2=2 :loc_format 1 -20 1=1 :India_Localization=:India_Localization Char Y xxen_util.jai_installed US India Localization 2 &il_columns_amt il_tax.cgst_amount "CGST Amount", il_tax.sgst_amount "SGST Amount", il_tax.igst_amount "IGST Amount", il_tax.cess_amount "CESS Amount", il_tax.custom_amount "Custom Amount", il_tax.unclassified_amount "Unclassified Tax Amount", il_tax.taxable_value "Taxable Value", il_tax.hsn_length_warning "HSN Length Warning", Y US India Localization 3 &il_columns_rate il_tax.cgst_tax_rate "CGST Rate", il_tax.sgst_tax_rate "SGST Rate", il_tax.igst_tax_rate "IGST Rate", il_tax.cess_tax_rate "CESS Rate", Y US India Localization 4 &il_columns_rec il_tax.cgst_rec_amount "CGST Recoverable", il_tax.cgst_nrec_amount "CGST Non-Recoverable", il_tax.sgst_rec_amount "SGST Recoverable", il_tax.sgst_nrec_amount "SGST Non-Recoverable", il_tax.igst_rec_amount "IGST Recoverable", il_tax.igst_nrec_amount "IGST Non-Recoverable", Y US India Localization 5 &il_columns_tp xxen_util.meaning(il_tax.reverse_charge_flag,'YES_NO',0) "Reverse Charge Flag", (select max(jtdf.ship_to_state) from jai_tax_det_factors jtdf where jtdf.trx_id=ooha.header_id and jtdf.application_id=660) "Place of Supply", (select max(jtdf.ship_from_state) from jai_tax_det_factors jtdf where jtdf.trx_id=ooha.header_id and jtdf.application_id=660) "Ship From State", (select max(jrc.reporting_code) from jai_tax_det_factors jtdf, jai_reporting_codes jrc where jtdf.trx_id=ooha.header_id and jtdf.trx_line_id=oola.line_id and jtdf.application_id=660 and nvl(jtdf.hsn_code_id,jtdf.sac_code_id)=jrc.reporting_code_id) "HSN SAC Code" Y US India Localization 6 &il_from ,(select jtlv.trx_id, sum(decode(jrav.reporting_code,'CGST',jtlv.rounded_tax_amt_trx_curr)) cgst_amount, sum(decode(jrav.reporting_code,'SGST',jtlv.rounded_tax_amt_trx_curr)) sgst_amount, sum(decode(jrav.reporting_code,'IGST',jtlv.rounded_tax_amt_trx_curr)) igst_amount, sum(decode(jrav.reporting_code,'CESS',jtlv.rounded_tax_amt_trx_curr)) cess_amount, sum(decode(jrav.reporting_code,'CUSTOM',jtlv.rounded_tax_amt_trx_curr)) custom_amount, sum(decode(jrav.reporting_code,null,jtlv.rounded_tax_amt_trx_curr)) unclassified_amount, max(decode(jrav.reporting_code,'CGST',jtlv.tax_rate_code)) cgst_tax_rate, max(decode(jrav.reporting_code,'SGST',jtlv.tax_rate_code)) sgst_tax_rate, max(decode(jrav.reporting_code,'IGST',jtlv.tax_rate_code)) igst_tax_rate, max(decode(jrav.reporting_code,'CESS',jtlv.tax_rate_code)) cess_tax_rate, sum(decode(jrav.reporting_code,'CGST',jtlv.rec_tax_amt_trx_curr)) cgst_rec_amount, sum(decode(jrav.reporting_code,'CGST',jtlv.nrec_tax_amt_trx_curr)) cgst_nrec_amount, sum(decode(jrav.reporting_code,'SGST',jtlv.rec_tax_amt_trx_curr)) sgst_rec_amount, sum(decode(jrav.reporting_code,'SGST',jtlv.nrec_tax_amt_trx_curr)) sgst_nrec_amount, sum(decode(jrav.reporting_code,'IGST',jtlv.rec_tax_amt_trx_curr)) igst_rec_amount, sum(decode(jrav.reporting_code,'IGST',jtlv.nrec_tax_amt_trx_curr)) igst_nrec_amount, max(jtlv.self_assessed_flag) reverse_charge_flag, sum(decode(jrav.reporting_code,'CGST',jtlv.rounded_taxable_amt_trx_curr,'IGST',jtlv.rounded_taxable_amt_trx_curr)) taxable_value, max(jrc.reporting_code) hsn_sac_code, max(case when length(jrc.reporting_code)<6 then 'Below 6 digits' end) hsn_length_warning from jai_tax_lines_v jtlv, jai_reporting_associations_v jrav, jai_tax_det_factors jtdf, jai_reporting_codes jrc where jtlv.tax_type_id=jrav.entity_id(+) and jrav.entity_code(+)='TAX_TYPE' and jrav.reporting_type_code(+)='TAX_TYPES_CLASSIFICATION' and jtlv.det_factor_id=jtdf.det_factor_id(+) and jtdf.application_id(+)=660 and nvl(jtdf.hsn_code_id,jtdf.sac_code_id)=jrc.reporting_code_id(+) and jtlv.application_id=660 group by jtlv.trx_id ) il_tax Y US India Localization 7 &il_reg_columns ,nvl(coalesce( ( select nvl(jprl.registration_number,jprl.secondary_registration_number) from hz_cust_site_uses_all hcsua, hz_cust_acct_sites_all hcasa, jai_party_regs_v jpr, jai_party_reg_lines_v jprl where ooha.invoice_to_org_id=hcsua.site_use_id and hcsua.cust_acct_site_id=hcasa.cust_acct_site_id and hcasa.cust_acct_site_id=jpr.party_site_id and jpr.party_type_code='THIRD_PARTY_SITE' and jpr.customer_flag='Y' and jpr.party_reg_id=jprl.party_reg_id and jprl.registration_type_code in ('GST','GSTIN') and jprl.regime_id in (select jr.regime_id from jai_regimes jr where jr.regime_type='T') and jprl.effective_from<=ooha.ordered_date and (jprl.effective_to is null or jprl.effective_to>=ooha.ordered_date) and rownum=1 ), ( select nvl(jprl.registration_number,jprl.secondary_registration_number) from hz_cust_site_uses_all hcsua, hz_cust_acct_sites_all hcasa, jai_party_regs_v jpr, jai_party_reg_lines_v jprl where ooha.invoice_to_org_id=hcsua.site_use_id and hcsua.cust_acct_site_id=hcasa.cust_acct_site_id and hcasa.cust_acct_site_id=jpr.party_site_id and jpr.party_type_code='THIRD_PARTY_SITE' and jpr.customer_flag='Y' and jpr.party_reg_id=jprl.party_reg_id and regexp_like(nvl(jprl.registration_number,jprl.secondary_registration_number),'^[0-9]{2}[A-Z]{5}[0-9]{4}[A-Z][0-9A-Z]Z[0-9A-Z]$') and jprl.regime_id in (select jr.regime_id from jai_regimes jr where jr.regime_type='T') and jprl.effective_from<=ooha.ordered_date and (jprl.effective_to is null or jprl.effective_to>=ooha.ordered_date) and rownum=1 )),coalesce( ( select nvl(jprl.registration_number,jprl.secondary_registration_number) from hz_cust_accounts hca0, jai_party_regs_v jpr, jai_party_reg_lines_v jprl where ooha.sold_to_org_id=hca0.cust_account_id and hca0.cust_account_id=jpr.party_id and jpr.party_reg_id=jprl.party_reg_id and jpr.party_type_code='THIRD_PARTY' and jpr.customer_flag='Y' and jprl.registration_type_code in ('GST','GSTIN') and jprl.regime_id in (select jr.regime_id from jai_regimes jr where jr.regime_type='T') and jprl.effective_from<=ooha.ordered_date and (jprl.effective_to is null or jprl.effective_to>=ooha.ordered_date) and rownum=1 ), ( select nvl(jprl.registration_number,jprl.secondary_registration_number) from hz_cust_accounts hca0, jai_party_regs_v jpr, jai_party_reg_lines_v jprl where ooha.sold_to_org_id=hca0.cust_account_id and hca0.cust_account_id=jpr.party_id and jpr.party_reg_id=jprl.party_reg_id and jpr.party_type_code='THIRD_PARTY' and jpr.customer_flag='Y' and regexp_like(nvl(jprl.registration_number,jprl.secondary_registration_number),'^[0-9]{2}[A-Z]{5}[0-9]{4}[A-Z][0-9A-Z]Z[0-9A-Z]$') and jprl.regime_id in (select jr.regime_id from jai_regimes jr where jr.regime_type='T') and jprl.effective_from<=ooha.ordered_date and (jprl.effective_to is null or jprl.effective_to>=ooha.ordered_date) and rownum=1 ))) "GST Registration No", ( select nvl(jprl.registration_number,jprl.secondary_registration_number) from hz_cust_accounts hca0, jai_party_regs_v jpr, jai_party_reg_lines_v jprl where ooha.sold_to_org_id=hca0.cust_account_id and hca0.cust_account_id=jpr.party_id and jpr.party_reg_id=jprl.party_reg_id and jpr.party_type_code='THIRD_PARTY' and jpr.customer_flag='Y' and jprl.registration_type_code='PAN' and jprl.effective_from<=ooha.ordered_date and (jprl.effective_to is null or jprl.effective_to>=ooha.ordered_date) and rownum=1 ) "GST PAN No", Y US India Localization 8 &il_where and ooha.header_id=il_tax.trx_id(+) Y US India Localization 9 10 1=1 haou2.name=:selling_ou LOV HR Operating Unit 8E2FF36EDEB979D2E0530100007F1FF2 Y select hou.name value, null description from hr_operating_units hou where sysdate between hou.date_from and nvl(hou.date_to,sysdate) and (:$flex$.ledger is null or hou.set_of_books_id in (select gl.ledger_id from gl_ledgers gl where xxen_util.contains(:$flex$.ledger,gl.name)='Y')) and hou.organization_id in (select xroa.id from xxen_report_org_access xroa where xroa.access_type='OU') order by hou.name coalesce(xxen_util.default_operating_unit,xxen_util.previous_parameter_value(:parameter_id)) F Unité opérationnelle de vente US Selling Operating Unit 10 20 1=1 haou1.name=:shipping_ou LOV HR Operating Unit 8E2FF36EDEB979D2E0530100007F1FF2 Y select hou.name value, null description from hr_operating_units hou where sysdate between hou.date_from and nvl(hou.date_to,sysdate) and (:$flex$.ledger is null or hou.set_of_books_id in (select gl.ledger_id from gl_ledgers gl where xxen_util.contains(:$flex$.ledger,gl.name)='Y')) and hou.organization_id in (select xroa.id from xxen_report_org_access xroa where xroa.access_type='OU') order by hou.name F Unité d'exploitation d'expédition US Shipping Operating Unit 11 30 1=1 ood.organization_code=:organization_code LOV custom Y select mp.organization_code value, ood.organization_name description from mtl_parameters mp, org_organization_definitions ood where mp.organization_id<>mp.master_organization_id and mp.organization_id in (select oav.organization_id from org_access_view oav where oav.resp_application_id=fnd_global.resp_appl_id and oav.responsibility_id=fnd_global.resp_id) and (:$flex$.shipping_operating_unit is null or ood.operating_unit in (select haouv.organization_id from hr_all_organization_units_vl haouv where xxen_util.contains(:$flex$.shipping_operating_unit,haouv.name)='Y')) and (:$flex$.ledger is null or ood.set_of_books_id in (select gl.ledger_id from gl_ledgers gl where xxen_util.contains(:$flex$.ledger,gl.name)='Y')) and mp.organization_id=ood.organization_id and nvl(ood.disable_date,sysdate)>=sysdate order by mp.organization_code select mp.organization_code from mtl_parameters mp where mp.organization_id = fnd_profile.value('MFG_ORGANIZATION_ID') F Code de l'organisation US Organization Code 12 40 1=1 wnd.delivery_id is not null LOV Oracle 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 Y Y F Affecté à la livraison US Assigned to Delivery 13 1=1 wnd.delivery_id is null N F Affecté à la livraison US Assigned to Delivery 14 50 1=1 wnd.status_code=xxen_util.lookup_code(:delivery_status,'DELIVERY_STATUS',665) LOV custom Y select wl.meaning value, null description from wsh_lookups wl where wl.lookup_type='DELIVERY_STATUS' and wl.enabled_flag='Y' and sysdate between nvl(wl.start_date_active,sysdate) and nvl(wl.end_date_active,sysdate) order by wl.meaning F État de la livraison US Delivery Status 15 60 1=1 nvl(wnd.status_code,'OP')<>xxen_util.lookup_code(:delivery_status_excl,'DELIVERY_STATUS',665) LOV custom Y select wl.meaning value, null description from wsh_lookups wl where wl.lookup_type='DELIVERY_STATUS' and wl.enabled_flag='Y' and sysdate between nvl(wl.start_date_active,sysdate) and nvl(wl.end_date_active,sysdate) order by wl.meaning Closed F Exclure l'état de la livraison US Exclude Delivery Status 16 70 1=1 wdd.released_status=xxen_util.lookup_code(:release_status,'PICK_STATUS',665) LOV custom Y select wl.meaning value, null description from wsh_lookups wl where wl.lookup_type='PICK_STATUS' and wl.enabled_flag='Y' and sysdate between nvl(wl.start_date_active,sysdate) and nvl(wl.end_date_active,sysdate) order by wl.meaning F État de la libération US Release status 17 80 1=1 wdd.released_status<>xxen_util.lookup_code(:release_status_excl,'PICK_STATUS',665) LOV custom Y select wl.meaning value, null description from wsh_lookups wl where wl.lookup_type='PICK_STATUS' and wl.enabled_flag='Y' and sysdate between nvl(wl.start_date_active,sysdate) and nvl(wl.end_date_active,sysdate) order by wl.meaning Cancelled F Exclure le statut de libération US Exclude Release Status 18 90 1=1 exists ( select null from wsh_exceptions_v wev where wev.delivery_detail_id = wdd.delivery_detail_id and status = 'OPEN' and severity = 'ERROR' ) LOV Oracle 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 Y F Existence d'une exception US Exception Exists 19 1=1 not exists ( select null from wsh_exceptions_v wev where wev.delivery_detail_id = wdd.delivery_detail_id and status = 'OPEN' and severity = 'ERROR' ) N F Existence d'une exception US Exception Exists 20 100 1=1 hp.party_name=:party LOV AR Customer Name 8E2FF36EDE8679D2E0530100007F1FF2 Y select hp.party_name value, hca.account_number||' ('||initcap(hp.party_type)||')' description from hz_parties hp, hz_cust_accounts hca where hp.party_id=hca.party_id and hca.cust_account_id in ( select hcasa.cust_account_id from hr_all_organization_units_vl haouv, hz_cust_acct_sites_all hcasa where xxen_util.contains(:$flex$.operating_unit,haouv.name)='Y' and haouv.organization_id=hcasa.org_id union all select hca2.cust_account_id from hz_cust_accounts hca2 where :$flex$.operating_unit is null ) order by hp.party_name F Nom du client US Customer Name 21 110 1=1 hca.account_number=:account_number LOV AR Account Number 8E2FF36EDE7F79D2E0530100007F1FF2 Y Y select hca.account_number value, hp.party_name||' ('||initcap(hp.party_type)||')' description from hz_parties hp, hz_cust_accounts hca where hp.party_id=hca.party_id and hca.cust_account_id in ( select hcasa.cust_account_id from hr_all_organization_units_vl haouv, hz_cust_acct_sites_all hcasa where xxen_util.contains(:$flex$.operating_unit,haouv.name)='Y' and haouv.organization_id=hcasa.org_id union all select hca2.cust_account_id from hz_cust_accounts hca2 where :$flex$.operating_unit is null ) order by hp.party_name F Numéro de client US Customer Number 22 120 1=1 wnd.name>=:delivery_from LOV custom select wnd.name value, null description from wsh_new_deliveries wnd where (:$flex$.organization_code is null or wnd.organization_id in (select mp.organization_id from mtl_parameters mp where xxen_util.contains(:$flex$.organization_code,mp.organization_code) = 'Y') ) and nvl(wnd.shipment_direction,'O') in ('O','IO') and wnd.delivery_type='STANDARD' order by wnd.name F Bon de livraison de US Delivery Note From 23 130 1=1 wnd.name<=:delivery_to LOV custom Y select wnd.name value, null description from wsh_new_deliveries wnd where (:$flex$.organization_code is null or wnd.organization_id in (select mp.organization_id from mtl_parameters mp where xxen_util.contains(:$flex$.organization_code,mp.organization_code) = 'Y') ) and nvl(wnd.shipment_direction,'O') in ('O','IO') and wnd.delivery_type='STANDARD' order by wnd.name $flex$.delivery_note_from F Note de livraison à US Delivery Note To 24 140 1=1 msiv.concatenated_segments=:item LOV INV Item 8E2FF36EDEF179D2E0530100007F1FF2 Y Y select msiv.concatenated_segments value, msiv.description||' ('||mp.organization_code||')' description from mtl_system_items_vl msiv, mtl_parameters mp where ( :$flex$.organization_code is null and ( fnd_profile.value('MFG_ORGANIZATION_ID') is null and msiv.organization_id in (select oav.organization_id from org_access_view oav where oav.resp_application_id=fnd_global.resp_appl_id and oav.responsibility_id=fnd_global.resp_id) or msiv.organization_id=fnd_profile.value('MFG_ORGANIZATION_ID') ) or xxen_util.contains(:$flex$.organization_code,mp.organization_code)='Y' ) and msiv.organization_id=mp.organization_id order by mp.organization_code, msiv.concatenated_segments US Item 25 150 1=1 msiv.concatenated_segments>=:item_from LOV INV Item 8E2FF36EDEF179D2E0530100007F1FF2 Y Y select msiv.concatenated_segments value, msiv.description||' ('||mp.organization_code||')' description from mtl_system_items_vl msiv, mtl_parameters mp where ( :$flex$.organization_code is null and ( fnd_profile.value('MFG_ORGANIZATION_ID') is null and msiv.organization_id in (select oav.organization_id from org_access_view oav where oav.resp_application_id=fnd_global.resp_appl_id and oav.responsibility_id=fnd_global.resp_id) or msiv.organization_id=fnd_profile.value('MFG_ORGANIZATION_ID') ) or xxen_util.contains(:$flex$.organization_code,mp.organization_code)='Y' ) and msiv.organization_id=mp.organization_id order by mp.organization_code, msiv.concatenated_segments F Article de US Item From 26 160 1=1 msiv.concatenated_segments<=:item_to LOV INV Item 8E2FF36EDEF179D2E0530100007F1FF2 Y Y select msiv.concatenated_segments value, msiv.description||' ('||mp.organization_code||')' description from mtl_system_items_vl msiv, mtl_parameters mp where ( :$flex$.organization_code is null and ( fnd_profile.value('MFG_ORGANIZATION_ID') is null and msiv.organization_id in (select oav.organization_id from org_access_view oav where oav.resp_application_id=fnd_global.resp_appl_id and oav.responsibility_id=fnd_global.resp_id) or msiv.organization_id=fnd_profile.value('MFG_ORGANIZATION_ID') ) or xxen_util.contains(:$flex$.organization_code,mp.organization_code)='Y' ) and msiv.organization_id=mp.organization_id order by mp.organization_code, msiv.concatenated_segments :$flex$.item_from F Objet à US Item To 27 170 &category_columns select xxen_util.item_category_columns(p_category_set_name=>'<parameter_value>') sql_text from dual LOV INV Category Set 8E2FF36EDECA79D2E0530100007F1FF2 Y select distinct mcsv.category_set_name value, mcsv.description from mtl_category_sets_vl mcsv where :$flex$.organization_code is null or mcsv.category_set_id in (select mic.category_set_id from mtl_parameters mp, mtl_item_categories mic where xxen_util.contains(:$flex$.organization_code,mp.organization_code)='Y' and mp.organization_id=mic.organization_id) order by mcsv.category_set_name coalesce(xxen_util.previous_parameter_value(:parameter_id),(select mdsv.category_set_name from mtl_default_sets_view mdsv where mdsv.functional_area_id=1)) US Category Set 1 28 180 &category_columns select xxen_util.item_category_columns(p_category_set_name=>'<parameter_value>') sql_text from dual LOV INV Category Set 8E2FF36EDECA79D2E0530100007F1FF2 Y select distinct mcsv.category_set_name value, mcsv.description from mtl_category_sets_vl mcsv where :$flex$.organization_code is null or mcsv.category_set_id in (select mic.category_set_id from mtl_parameters mp, mtl_item_categories mic where xxen_util.contains(:$flex$.organization_code,mp.organization_code)='Y' and mp.organization_id=mic.organization_id) order by mcsv.category_set_name coalesce(xxen_util.previous_parameter_value(:parameter_id),(select mdsv.category_set_name from mtl_default_sets_view mdsv where mdsv.functional_area_id=2)) US Category Set 2 29 190 &category_columns select xxen_util.item_category_columns(p_category_set_name=>'<parameter_value>') sql_text from dual LOV INV Category Set 8E2FF36EDECA79D2E0530100007F1FF2 Y select distinct mcsv.category_set_name value, mcsv.description from mtl_category_sets_vl mcsv where :$flex$.organization_code is null or mcsv.category_set_id in (select mic.category_set_id from mtl_parameters mp, mtl_item_categories mic where xxen_util.contains(:$flex$.organization_code,mp.organization_code)='Y' and mp.organization_id=mic.organization_id) order by mcsv.category_set_name xxen_util.previous_parameter_value(:parameter_id) US Category Set 3 30 200 1=1 (wdd.lot_number=:lot_number or nvl(shipped_lots.lot_numbers,picked_lots.lot_numbers) is not null) LOV INV Lot Number 2450E07B1B9F64E9E0630100007FCFA7 Y select distinct mln.lot_number value, mln.description from mtl_parameters mp, mtl_system_items_vl msiv, mtl_lot_numbers mln where mp.organization_id = msiv.organization_id and mln.organization_id = msiv.organization_id and mln.inventory_item_id = msiv.inventory_item_id and (:$flex$.organization_code is null and (fnd_profile.value('MFG_ORGANIZATION_ID') is null and msiv.organization_id in (select oav.organization_id from org_access_view oav where oav.resp_application_id=fnd_global.resp_appl_id and oav.responsibility_id=fnd_global.resp_id) or msiv.organization_id=fnd_profile.value('MFG_ORGANIZATION_ID') ) or xxen_util.contains(:$flex$.organization_code,mp.organization_code)='Y' ) and (:$flex$.item is null or xxen_util.contains(:$flex$.item,msiv.concatenated_segments) = 'Y') and (:$flex$.item_from is null or msiv.concatenated_segments >= :$flex$.item_from) and (:$flex$.item_to is null or msiv.concatenated_segments <= :$flex$.item_to) order by value, description US Lot Number 31 2=2 mtln.lot_number=:lot_number US Lot Number 32 210 1=1 (wdd.lot_number>=:lot_number_from or nvl(shipped_lots.lot_numbers,picked_lots.lot_numbers) is not null) LOV INV Lot Number 2450E07B1B9F64E9E0630100007FCFA7 Y select distinct mln.lot_number value, mln.description from mtl_parameters mp, mtl_system_items_vl msiv, mtl_lot_numbers mln where mp.organization_id = msiv.organization_id and mln.organization_id = msiv.organization_id and mln.inventory_item_id = msiv.inventory_item_id and (:$flex$.organization_code is null and (fnd_profile.value('MFG_ORGANIZATION_ID') is null and msiv.organization_id in (select oav.organization_id from org_access_view oav where oav.resp_application_id=fnd_global.resp_appl_id and oav.responsibility_id=fnd_global.resp_id) or msiv.organization_id=fnd_profile.value('MFG_ORGANIZATION_ID') ) or xxen_util.contains(:$flex$.organization_code,mp.organization_code)='Y' ) and (:$flex$.item is null or xxen_util.contains(:$flex$.item,msiv.concatenated_segments) = 'Y') and (:$flex$.item_from is null or msiv.concatenated_segments >= :$flex$.item_from) and (:$flex$.item_to is null or msiv.concatenated_segments <= :$flex$.item_to) order by value, description US Lot Number From 33 2=2 mtln.lot_number>=:lot_number_from US Lot Number From 34 220 1=1 (wdd.lot_number<=:lot_number_to or nvl(shipped_lots.lot_numbers,picked_lots.lot_numbers) is not null) LOV INV Lot Number 2450E07B1B9F64E9E0630100007FCFA7 Y select distinct mln.lot_number value, mln.description from mtl_parameters mp, mtl_system_items_vl msiv, mtl_lot_numbers mln where mp.organization_id = msiv.organization_id and mln.organization_id = msiv.organization_id and mln.inventory_item_id = msiv.inventory_item_id and (:$flex$.organization_code is null and (fnd_profile.value('MFG_ORGANIZATION_ID') is null and msiv.organization_id in (select oav.organization_id from org_access_view oav where oav.resp_application_id=fnd_global.resp_appl_id and oav.responsibility_id=fnd_global.resp_id) or msiv.organization_id=fnd_profile.value('MFG_ORGANIZATION_ID') ) or xxen_util.contains(:$flex$.organization_code,mp.organization_code)='Y' ) and (:$flex$.item is null or xxen_util.contains(:$flex$.item,msiv.concatenated_segments) = 'Y') and (:$flex$.item_from is null or msiv.concatenated_segments >= :$flex$.item_from) and (:$flex$.item_to is null or msiv.concatenated_segments <= :$flex$.item_to) order by value, description US Lot Number To 35 2=2 mtln.lot_number<=:lot_number_to US Lot Number To 36 230 1=1 wdd.source_document_number>=:order_num_from LOV custom Y Y select to_char(ooha.order_number) value, ottt.name||': '||hca.account_name||' ('||xxen_util.meaning(ooha.flow_status_code,'FLOW_STATUS',660)||')' description from oe_order_headers_all ooha, oe_transaction_types_tl ottt, hz_cust_accounts hca where nvl(ooha.transaction_phase_code,'F')='F' and ((:$flex$.selling_operating_unit is null and ooha.org_id in (select mgoat.organization_id from mo_glob_org_access_tmp mgoat union select fnd_global.org_id from dual where fnd_release.major_version=11) ) or ooha.org_id in (select haouv.organization_id from hr_all_organization_units_vl haouv where xxen_util.contains(:$flex$.selling_operating_unit,haouv.name)='Y') ) and ooha.sold_to_org_id=hca.cust_account_id and ooha.order_type_id=ottt.transaction_type_id and ottt.language=userenv('lang') and exists (select null from wsh_delivery_details wdd where wdd.source_header_number = to_char(ooha.order_number) ) order by to_char(ooha.order_number) desc F Numéro de commande De US Order Number From 37 240 1=1 wdd.source_document_number<=:order_num_to LOV custom Y Y select to_char(ooha.order_number) value, ottt.name||': '||hca.account_name||' ('||xxen_util.meaning(ooha.flow_status_code,'FLOW_STATUS',660)||')' description from oe_order_headers_all ooha, oe_transaction_types_tl ottt, hz_cust_accounts hca where nvl(ooha.transaction_phase_code,'F')='F' and ((:$flex$.selling_operating_unit is null and ooha.org_id in (select mgoat.organization_id from mo_glob_org_access_tmp mgoat union select fnd_global.org_id from dual where fnd_release.major_version=11) ) or ooha.org_id in (select haouv.organization_id from hr_all_organization_units_vl haouv where xxen_util.contains(:$flex$.selling_operating_unit,haouv.name)='Y') ) and ooha.sold_to_org_id=hca.cust_account_id and ooha.order_type_id=ottt.transaction_type_id and ottt.language=userenv('lang') and exists (select null from wsh_delivery_details wdd where wdd.source_header_number = to_char(ooha.order_number) ) order by to_char(ooha.order_number) desc :$flex$.order_number_to F Numéro de commande à US Order Number To 38 250 1=1 wdd.date_scheduled>=:sched_date_from Date F Date prévue du US Scheduled Date From 39 260 1=1 wdd.date_scheduled<:sched_date_to+1 Date :$flex$.scheduled_date_from F Date prévue jusqu'au US Scheduled Date To 40 270 1=1 wpb.creation_date>=:release_date_from Date F Date de sortie de US Released Date From 41 280 1=1 wpb.creation_date<:release_date_to+1 Date :$flex$.released_date_from F Date de sortie au US Released Date To 42 290 1=1 mmt.transaction_date>=:ship_date_from Date F Date d'expédition réelle De US Actual Shipment Date From 43 300 1=1 mmt.transaction_date<:ship_date_to+1 Date :$flex$.actual_shipment_date_from F Date réelle d'expédition à US Actual Shipment Date To 44 310 1=1 rcta.trx_date>=:ar_trx_date_from Date F Date de facturation de US Invoiced Date From 45 320 1=1 rcta.trx_date<:ar_trx_date_to+1 Date :$flex$.invoiced_date_from F Date de facturation au US Invoiced Date To 46 330 1=1 wnd.initial_pickup_date>=:pickup_date_from Date F Date de prise en charge initiale du US Initial Pick Up Date From 47 340 1=1 wnd.initial_pickup_date<:pickup_date_to+1 Date :$flex$.initial_pick_up_date_from F Date de prise en charge initiale au US Initial Pick Up Date To 48 350 1=1 wnd.ultimate_dropoff_date>=:dropoff_date_from Date F Date de dépôt ultime De US Ultimate Drop Off Date From 49 360 1=1 wnd.ultimate_dropoff_date<:dropoff_date_to+1 Date :$flex$.ultimate_drop_off_date_from F Date de dépôt ultime à US Ultimate Drop Off Date To 50 370 :loc_format LOV custom Y select x.value, x.description from ( select 'City State Zip' value, 'City, State, Zip' description from dual union select 'City State Zip Country' value, 'City, State, Zip, Country' description from dual union select 'Code City' value,'Location Code, City' description from dual union select 'Code Address1 City' value,'Location Code, Address1, City' description from dual ) x order by x.value Code City US Location Format