[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT
NULL cost_group,
item.concatenated_segments,
item.description,
item.attribute1,
item.attribute10,
item.attribute11,
item.attribute12,
item.attribute13,
item.attribute14,
item.attribute15,
item.attribute2,
item.attribute3,
item.attribute4,
item.attribute5,
item.attribute6,
item.attribute7,
item.attribute8,
item.attribute9,
item.attribute_category,
item_poh. hazard_class,
item_mir.revision
FROM MTL_SYSTEM_ITEMS_KFV item,
PO_HAZARD_CLASSES item_poh,
MTL_ITEM_REVISIONS item_mir
WHERE item.organization_id = p_organization_id
AND item.inventory_item_id = p_inventory_item_id
AND item.hazard_class_id = item_poh.hazard_class_id (+)
AND item.organization_Id = item_mir.organization_id(+)
AND item.inventory_item_id = item_mir.inventory_item_id(+);
SELECT
wflow.bom_revision,
Wflow.build_sequence,
wflow_loc.concatenated_segments completion_location,
NVL(p_subinventory_code ,wflow.completion_subinventory),
wflow.end_item_unit_number,
wflow.attribute1,
wflow.attribute10,
wflow.attribute11,
wflow.attribute12,
wflow.attribute13,
wflow.attribute14,
wflow.attribute15,
wflow.attribute2,
wflow.attribute3,
wflow.attribute4,
wflow.attribute5,
wflow.attribute6,
wflow.attribute7,
wflow.attribute8,
wflow.attribute9,
wflow.attribute_category,
Wflow.created_by,
to_char(wflow.creation_date,G_DATE_FORMAT_MASK),
to_char(wflow.last_update_date,G_DATE_FORMAT_MASK),
wflow.last_updated_by,
wflow.planned_quantity,
wflow.quantity_completed,
wflow.schedule_number,
to_char(wflow.scheduled_start_date,G_DATE_FORMAT_MASK),
wflow.status,
wflow_mkc.kanban_card_number,
wflow.material_account,
wflow.mps_net_quantity,
to_char(wflow.mps_scheduled_completion_date,G_DATE_FORMAT_MASK),
wflow.quantity_scrapped,
wflow.routing_revision,
to_char(wflow.scheduled_completion_date,G_DATE_FORMAT_MASK),
wflow_we.wip_entity_name,
Wflow_wl.line_code,
wflow.End_item_unit_number,
wflow.Current_line_operation
FROM WIP_FLOW_SCHEDULES wflow,
WIP_ENTITIES wflow_we,
MTL_ITEM_LOCATIONS_KFV wflow_loc,
MTL_KANBAN_CARDS wflow_mkc,
WIP_LINES wflow_wl
WHERE wflow.wip_entity_id = l_wip_entity_id
AND Wflow.wip_entity_id = wflow_we.wip_entity_id
AND NVL(p_locator_id,wflow.completion_locator_id) = wflow_loc.inventory_location_id(+)
AND Wflow.kanban_card_id = wflow_mkc.kanban_card_id(+)
AND Wflow.line_id = wflow_wl.line_id(+);
SELECT wip_entity_id
FROM WIP_FLOW_SCHEDULES
WHERE wip_entity_id = p_wip_entity_id ;
SELECT wip_entity_id INTO l_wip_entity_id
FROM WIP_FLOW_SCHEDULES
WHERE schedule_number = p_schedule_number ;
SELECT
--Bom_hdr.bill_sequence_id,
--Bom_hdr.assembly_item_id,
bom_hdr.attribute1,
bom_hdr.attribute2,
bom_hdr.attribute3,
bom_hdr.attribute4,
bom_hdr.attribute5,
bom_hdr.attribute6,
bom_hdr.attribute7,
bom_hdr.attribute8,
bom_hdr.attribute9,
bom_hdr.attribute10,
bom_hdr.attribute11,
bom_hdr.attribute12,
bom_hdr.attribute13,
bom_hdr.attribute14,
bom_hdr.attribute15,
bom_hdr.attribute_category,
bom_hdr_pp.name project_name,
bom_hdr_pt.task_name task_name,
Bom_hdr.specific_assembly_comment
FROM BOM_BILL_OF_MATERIALS bom_hdr,
PA_PROJECTS bom_hdr_pp, PA_TASKS bom_hdr_pt
WHERE bom_hdr.assembly_item_id = p_inventory_item_id
AND bom_hdr.organization_id = p_organization_id
AND nvl(bom_hdr.alternate_bom_designator, '@@@') = nvl(p_alternate_bom_designator, '@@@')
AND bom_hdr.project_id = bom_hdr_pp.project_id(+)
AND bom_hdr.task_id = bom_hdr_pt.task_id(+);
SELECT
--bom_rte.routing_sequence_id,
--bom_rte.assembly_item_id,
bom_rte.attribute1,
bom_rte.attribute2,
bom_rte.attribute3,
bom_rte.attribute4,
bom_rte.attribute5,
bom_rte.attribute6,
bom_rte.attribute7,
bom_rte.attribute8,
bom_rte.attribute9,
bom_rte.attribute10,
bom_rte.attribute11,
bom_rte.attribute12,
bom_rte.attribute13,
bom_rte.attribute14,
bom_rte.attribute15,
bom_rte.attribute_category,
bom_rte.routing_comment,
bom_rte_wl.line_code line_code,
bom_rte.total_product_cycle_time,
bom_rte_pp.name project_name,
bom_rte_pt.task_name
FROM BOM_OPERATIONAL_ROUTINGS bom_rte,
PA_PROJECTS bom_rte_pp, PA_TASKS bom_rte_pt,
WIP_LINES bom_rte_wl
WHERE bom_rte.assembly_item_id = p_inventory_item_id
AND bom_rte.organization_id = p_organization_id
AND nvl(bom_rte.alternate_routing_designator, '@@@') = nvl(p_alternate_routing_designator, '@@@')
AND bom_rte.project_id = bom_rte_pp.project_id(+)
AND bom_rte.task_id = bom_rte_pt.task_id(+)
AND bom_rte.line_id = bom_rte_wl.line_id(+);
SELECT
kanban.attribute1,
kanban.attribute2,
kanban.attribute3,
kanban.attribute4,
Kanban.attribute5,
kanban.attribute6,
kanban.attribute7,
kanban.attribute8,
kanban.attribute9,
kanban.attribute10,
kanban.attribute11,
kanban.attribute12,
kanban.attribute13,
kanban.attribute14,
kanban.attribute15,
kanban.attribute_category,
kanban.card_status,
kanban.kanban_card_type,
kanban.created_by,
to_char(kanban.creation_date,G_DATE_FORMAT_MASK),
kanban.last_updated_by,
to_char(kanban.last_update_date,G_DATE_FORMAT_MASK),
kanban_loc.concatenated_segments locator,
kanban.pull_sequence_id,
kanban.kanban_size,
kanban_sloc.concatenated_segments source_locator,
kanban_sorg.organization_code source_organization_code,
kanban.source_subinventory,
kanban.Source_type,
pv.vendor_name supplier,
kanban.supply_status,
kanban.kanban_card_number
FROM MTL_KANBAN_CARDS kanban,
MTL_ITEM_LOCATIONS_KFV kanban_loc,
MTL_ITEM_LOCATIONS_KFV kanban_sloc,
MTL_PARAMETERS kanban_sorg,
MTL_PARAMETERS kanban_org,
PO_VENDORS pv
WHERE kanban.kanban_card_id = p_kanban_card_id
AND kanban.locator_id = kanban_loc.inventory_location_id(+)
AND kanban.source_locator_id = kanban_sloc.inventory_location_id(+)
AND kanban.source_organization_id = kanban_sorg.organization_id
AND kanban.organization_id = kanban_org.organization_id
AND kanban.supplier_id = pv.vendor_id(+);
SELECT
lot.lot_number,
lot.age,
to_char(lot.best_by_date,G_DATE_FORMAT_MASK),
lot.c_attribute1,
lot.c_attribute10,
lot.c_attribute11,
lot.c_attribute12,
lot.c_attribute13,
lot.c_attribute14,
lot.c_attribute15,
lot.c_attribute16,
lot.c_attribute17,
lot.c_attribute18,
lot.c_attribute19,
lot.c_attribute2,
lot.c_attribute20,
lot.c_attribute3,
lot.c_attribute4,
lot.c_attribute5,
lot.c_attribute6,
lot.c_attribute7,
lot.c_attribute8,
lot.c_attribute9,
lot.attribute_category,
to_char(lot.change_date,G_DATE_FORMAT_MASK),
lot.color,
lot.d_attribute1,
lot.d_attribute10,
lot.d_attribute2,
lot.d_attribute3,
lot.d_attribute4,
lot.d_attribute5,
lot.d_attribute6,
lot.d_attribute7,
lot.d_attribute8,
lot.d_attribute9,
lot.date_code,
lot.grade_code,
lot.item_size,
lot.length,
lot.length_uom,
to_char(lot.maturity_date,G_DATE_FORMAT_MASK),
lot.n_attribute1,
lot.n_attribute10,
lot.n_attribute2,
lot.n_attribute3,
lot.n_attribute4,
lot.n_attribute5,
lot.n_attribute6,
lot.n_attribute7,
lot.n_attribute8,
lot.n_attribute9,
to_char(lot.origination_date,G_DATE_FORMAT_MASK),
lot.place_of_origin,
lot.recycled_content,
to_char(lot.retest_date,G_DATE_FORMAT_MASK),
lot.thickness,
lot.thickness_uom,
lot.vendor_name supplier,
lot.supplier_lot_number,
lot.volume,
lot.volume_uom,
lot.width,
lot.width_uom,
to_char(lot.expiration_date,G_DATE_FORMAT_MASK),
lot_status.status_code
FROM MTL_LOT_NUMBERS lot,
MTL_MATERIAL_STATUSES_VL lot_status
WHERE lot.lot_number = p_lot_number
AND lot.status_id = lot_status.status_id (+);
SELECT
serial.c_attribute1,
serial.c_attribute2,
serial.c_attribute3,
serial.c_attribute4,
serial.c_attribute5,
serial.c_attribute6,
serial.c_attribute7,
serial.c_attribute8,
serial.c_attribute9,
serial.c_attribute10,
serial.c_attribute11,
serial.c_attribute12,
serial.c_attribute13,
serial.c_attribute14,
serial.c_attribute15,
serial.c_attribute16,
serial.c_attribute17,
serial.c_attribute18,
serial.c_attribute19,
serial.c_attribute20,
serial.attribute_category,
to_date(serial.completion_date,G_DATE_FORMAT_MASK),
serial.cycles_since_mark,
serial.cycles_since_new,
serial.cycles_since_overhaul,
serial.cycles_since_repair,
serial.cycles_since_visit,
serial.d_attribute1,
serial.d_attribute10,
serial.d_attribute2,
serial.d_attribute3,
serial.d_attribute4,
serial.d_attribute5,
serial.d_attribute6,
serial.d_attribute7,
serial.d_attribute8,
serial.d_attribute9,
serial.fixed_asset_tag,
to_char(serial.initialization_date,G_DATE_FORMAT_MASK),
serial.n_attribute1,
serial.n_attribute2,
serial.n_attribute3,
serial.n_attribute4,
serial.n_attribute5,
serial.n_attribute6,
serial.n_attribute7,
serial.n_attribute8,
serial.n_attribute9,
serial.n_attribute10,
serial.number_of_repairs,
to_char(serial.origination_date,G_DATE_FORMAT_MASK),
serial.time_since_mark,
serial.time_since_new,
serial.time_since_overhaul,
serial.time_since_repair,
serial.time_since_visit,
serial.vendor_serial_number,
serial.serial_number,
serial_status.status_code
FROM MTL_SERIAL_NUMBERS serial,
MTL_MATERIAL_STATUSES_VL serial_status
WHERE serial.inventory_item_id = p_inventory_item_id
AND serial.serial_number = p_serial_number
AND serial.status_id = serial_status.status_id (+);
SELECT
lpn.license_plate_number license_plate_number,
lpn_msik1.concatenated_segments lpn_container_item,
lpn.attribute1,
lpn.attribute2,
lpn.attribute3,
lpn.attribute4,
lpn.attribute5,
lpn.attribute6,
lpn.attribute7,
lpn.attribute8,
lpn.attribute9,
lpn.attribute10,
lpn.attribute11,
lpn.attribute12,
lpn.attribute13,
lpn.attribute14,
lpn.attribute15,
lpn.attribute_category,
lpn.gross_weight gross_weight,
lpn.gross_weight_uom_code gross_weight_uom,
0 number_of_total,
lpn.tare_weight tare_weight,
lpn.tare_weight_uom_code tare_weight_uom,
0 total_of_total,
lpn.content_volume volume,
lpn.content_volume_uom_code volume_uom,
lpn_mp.organization_code organization,
lpn_msik2.concatenated_segments item,
lpn_msik2.description item_description,
lpn_wlc.revision revision,
lpn_wlc.lot_number lot,
NVL(lpn_wlc.serial_number, p_serial_number) serial_number,
decode(p_serial_number, NULL, lpn_wlc.quantity,
decode(lpn_wlc.serial_summary_entry, 1, 1, lpn_wlc.quantity)) quantity,
lpn_wlc.uom_code
FROM wms_license_plate_numbers lpn,
wms_license_plate_numbers lpn_pLpn,
mtl_system_items_kfv lpn_msik1,
mtl_system_items_kfv lpn_msik2,
mtl_parameters lpn_mp,
wms_lpn_contents lpn_wlc
WHERE lpn.lpn_id = p_lpn_id
AND lpn.parent_lpn_id = lpn_pLpn.lpn_id(+)
AND lpn_wlc.parent_lpn_id(+) = p_lpn_id
AND nvl(lpn_wlc.revision, '$$$') = nvl(p_revision, nvl(lpn_wlc.revision, '$$$'))
AND nvl(lpn_wlc.lot_number, '$$$') = nvl(p_lot_number, nvl(lpn_wlc.lot_number, '$$$'))
--AND nvl(lpn_wlc.serial_number,'$$$') = nvl(p_serial_number,nvl(lpn_wlc.serial_number,'$$$'))
AND lpn_wlc.inventory_item_id = p_inventory_item_id
AND lpn_msik1.inventory_item_id (+) = lpn.inventory_item_id
AND lpn_msik1.organization_id (+) = lpn.organization_id
AND lpn_mp.organization_id = lpn.organization_id
AND lpn_msik2.inventory_item_id(+) = lpn_wlc.inventory_item_id
AND lpn_msik2.organization_id(+) = lpn_wlc.organization_id;
SELECT
ohead_mp.organization_code,
to_char(Ohead.booked_date,G_DATE_FORMAT_MASK),
Ohead.credit_card_holder_name,
Ohead.credit_card_number,
to_char(Ohead.expiration_date,G_DATE_FORMAT_MASK),
ohead.attribute1,
ohead.attribute10,
ohead.attribute11,
ohead.attribute12,
ohead.attribute13,
ohead.attribute14,
ohead.attribute15,
ohead.attribute2,
ohead.attribute3,
ohead.attribute4,
ohead.attribute5,
ohead.attribute6,
ohead.attribute7,
ohead.attribute8,
ohead.attribute9,
ohead.global_attribute_category,
ohead.global_attribute1,
ohead.global_attribute10,
ohead.global_attribute11,
ohead.global_attribute12,
ohead.global_attribute13,
ohead.global_attribute14,
ohead.global_attribute15,
ohead.global_attribute16,
ohead.global_attribute17,
ohead.global_attribute18,
ohead.global_attribute19,
ohead.global_attribute2,
ohead.global_attribute20,
ohead.global_attribute3,
ohead.global_attribute4,
ohead.global_attribute5,
ohead.global_attribute6,
ohead.global_attribute7,
ohead.global_attribute8,
ohead.global_attribute9,
Ohead.order_number,
Ohead.return_reason_code,
to_char(Ohead.ordered_date,G_DATE_FORMAT_MASK),
ohead_rcs.customer_name,
ohead_rcs.person_first_name,
ohead_rcs.person_last_name,
ohead_rcs.person_middle_name,
ohead_rcs.customer_type,
ohead_rcs.customer_id,
ohead_rcs.party_id,
ohead_rcs.party_number,
ohead.sold_from_org_id,
ohead.sold_to_org_id,
ohead.ship_to_org_id,
ohead.ship_from_org_id,
ohead.invoice_to_org_id,
ohead.deliver_to_org_id
FROM OE_ORDER_HEADERS_ALL ohead
, MTL_PARAMETERS ohead_mp
--
-- Modification Start for Bug # - 4418524
--
-- As part of TCA related changes ra_customers, ra_contacts views are
-- obsoleted in R12. The columns fetched from these views are fetched
-- from hz_parties and hz_cust_accounts.
--
-- Following table alias are commented
--, ra_customers ohead_rcs
--
-- Following Queries are added to replace the above commented
-- views
--
, ( SELECT CUST_ACCT.cust_account_id customer_id,
PARTY.party_id party_id,
PARTY.party_number party_number,
SUBSTRB(PARTY.party_name,1,50) customer_name,
PARTY.person_first_name person_first_name,
PARTY.person_middle_name person_middle_name,
PARTY.person_last_name person_last_name,
CUST_ACCT.customer_type customer_type
FROM hz_parties PARTY
, hz_cust_accounts CUST_ACCT
WHERE CUST_ACCT.party_id = PARTY.party_id
) ohead_rcs
--
-- Modification End for Bug # - 4418524
--
WHERE ohead.header_id = l_header_id
AND ohead.org_id = ohead_mp.organization_id
AND ohead.sold_to_org_id = ohead_rcs.customer_id(+);
SELECT
oline.booked_flag,
oline.cancelled_flag,
oline.component_code,
oline.cust_po_number,
to_char(oline.earliest_acceptable_date,G_DATE_FORMAT_MASK),
to_char(oline.explosion_date, G_DATE_FORMAT_MASK),
oline.freight_carrier_code,
to_char(oline.latest_acceptable_date, G_DATE_FORMAT_MASK),
Oline.open_flag,
to_char(oline.actual_shipment_date, G_DATE_FORMAT_MASK),
oline.created_by,
oline.last_updated_by,
to_char(oline.last_update_date, G_DATE_FORMAT_MASK),
oline.attribute1,
oline.attribute2,
oline.attribute3,
oline.attribute4,
oline.attribute5,
oline.attribute6,
oline.attribute7,
oline.attribute8,
oline.attribute9,
oline.attribute10,
oline.attribute11,
oline.attribute12,
oline.attribute13,
oline.attribute14,
oline.attribute15,
oline.return_attribute1,
oline.return_attribute2,
oline.return_attribute3,
oline.return_attribute4,
oline.return_attribute5,
oline.return_attribute6,
oline.return_attribute7,
oline.return_attribute8,
oline.return_attribute9,
oline.return_attribute10,
oline.return_attribute11,
oline.return_attribute12,
oline.return_attribute13,
oline.return_attribute14,
oline.return_attribute15,
oline.return_context,
oline.context,
to_char(oline.creation_date, G_DATE_FORMAT_MASK),
oline.fulfilled_quantity,
oline.ordered_item,
oline.line_number,
oline.ordered_quantity,
to_char(oline.promise_date, G_DATE_FORMAT_MASK),
oline.order_quantity_uom,
to_char(oline.request_date, G_DATE_FORMAT_MASK),
to_char(oline.schedule_ship_date, G_DATE_FORMAT_MASK),
oline.shipped_quantity,
oline.shipping_quantity,
oline.shipping_quantity_uom,
oline.over_ship_reason_code,
oline.packing_instructions,
pp.name project_name,
ras.name salesreps_name,
to_char(oline.schedule_arrival_date, G_DATE_FORMAT_MASK),
oe_sets.set_name ship_set_name,
osmv.meaning shipping_method_name,
ar_lookups.meaning tax_exempt_reason,
oline.tax_code,
oline.tax_exempt_flag,
oline.tax_exempt_number,
oline.tax_rate,
oline.shipment_number,
oline.shipping_instructions,
--rcship.last_name,
LTRIM(rcship.last_name ||decode(rcship.first_name,NULL,NULL,','||rcship.first_name)) ship_to_contact_name,
LTRIM(isc.last_name || decode(isc.first_name,NULL,NULL,','|| isc.first_name)) intmed_ship_to_contact_name,
LTRIM(invc.last_name || decode(invc.first_name,NULL,NULL,','|| invc.first_name)) invoice_to_contact_name,
LTRIM(dcontact.last_name || decode(dcontact.first_name,NULL,NULL,','|| dcontact.first_name)) deliver_to_contact_name,
oline_rc.customer_name,
oline_rc.person_first_name,
oline_rc.person_last_name,
oline_rc.person_middle_name,
oline_rc.customer_type,
oline_rc.customer_id,
oline_rc.party_id,
oline_rc.party_number,
oline.sold_from_org_id,
oline.sold_to_org_id,
oline.ship_from_org_id,
oline.ship_to_org_id,
oline.invoice_to_org_id,
oline.deliver_to_org_id,
shiptoc.customer_id ship_to_customer_id,
dcontact.customer_id delivery_customer_id,
invc.customer_id invoice_customer_id,
oline.header_id
-- ship_from_org.organization_code
FROM OE_ORDER_LINES_ALL oline,
OE_SHIP_METHODS_V osmv
--
-- Modification Start for Bug # - 4418524
--
-- As part of TCA related changes ra_customers, ra_contacts views are
-- obsoleted in R12. The columns fetched from these views are fetched
-- from "HZ_PARTIES", "HZ_CUST_ACCOUNTS", "HZ_CUST_ACCOUNT_ROLES",
-- "HZ_CUST_ACCOUNTS", "HZ_RELATIONSHIPS".
--
-- Following six table alias are commented
--, ra_customers oline_rc
--, ra_contacts rcship
--, ra_contacts dcontact
--, ra_contacts isc
--, ra_contacts invc
--, ra_contacts shiptoc
--
-- Following 4 Queries are added to replace the above commented
-- views
--
, ( SELECT CUST_ACCT.cust_account_id customer_id,
PARTY.party_id party_id,
PARTY.party_number party_number,
SUBSTRB(PARTY.party_name,1,50) customer_name,
PARTY.person_first_name person_first_name,
PARTY.person_middle_name person_middle_name,
PARTY.person_last_name person_last_name,
CUST_ACCT.customer_type customer_type
FROM hz_parties PARTY
, hz_cust_accounts CUST_ACCT
WHERE CUST_ACCT.party_id = PARTY.party_id
) oline_rc
, ( SELECT ACCT_ROLE.cust_account_role_id contact_id,
ACCT_ROLE.cust_account_id customer_id,
SUBSTRB(PARTY.person_last_name,1,50) last_name,
SUBSTRB(PARTY.person_first_name,1,40) first_name
FROM hz_cust_account_roles ACCT_ROLE,
hz_parties PARTY,
hz_relationships REL,
hz_cust_accounts ROLE_ACCT
WHERE
ACCT_ROLE.party_id = REL.party_id
AND ACCT_ROLE.role_type = 'CONTACT'
AND REL.subject_id = PARTY.party_id
AND REL.subject_table_name = 'HZ_PARTIES'
AND REL.object_table_name = 'HZ_PARTIES'
AND ACCT_ROLE.cust_account_id = ROLE_ACCT.cust_account_id
AND ROLE_ACCT.party_id = REL.object_id
) rcship
, ( SELECT ACCT_ROLE.cust_account_role_id contact_id,
ACCT_ROLE.cust_account_id customer_id,
SUBSTRB(PARTY.person_last_name,1,50) last_name,
SUBSTRB(PARTY.person_first_name,1,40) first_name
FROM hz_cust_account_roles ACCT_ROLE,
hz_parties PARTY,
hz_relationships REL,
hz_cust_accounts ROLE_ACCT
WHERE
ACCT_ROLE.party_id = REL.party_id
AND ACCT_ROLE.role_type = 'CONTACT'
AND REL.subject_id = PARTY.party_id
AND REL.subject_table_name = 'HZ_PARTIES'
AND REL.object_table_name = 'HZ_PARTIES'
AND ACCT_ROLE.cust_account_id = ROLE_ACCT.cust_account_id
AND ROLE_ACCT.party_id = REL.object_id
) dcontact
, ( SELECT ACCT_ROLE.cust_account_role_id contact_id,
ACCT_ROLE.cust_account_id customer_id,
SUBSTRB(PARTY.person_last_name,1,50) last_name,
SUBSTRB(PARTY.person_first_name,1,40) first_name
FROM hz_cust_account_roles ACCT_ROLE,
hz_parties PARTY,
hz_relationships REL,
hz_cust_accounts ROLE_ACCT
WHERE
ACCT_ROLE.party_id = REL.party_id
AND ACCT_ROLE.role_type = 'CONTACT'
AND REL.subject_id = PARTY.party_id
AND REL.subject_table_name = 'HZ_PARTIES'
AND REL.object_table_name = 'HZ_PARTIES'
AND ACCT_ROLE.cust_account_id = ROLE_ACCT.cust_account_id
AND ROLE_ACCT.party_id = REL.object_id
) isc
, ( SELECT ACCT_ROLE.cust_account_role_id contact_id,
ACCT_ROLE.cust_account_id customer_id,
SUBSTRB(PARTY.person_last_name,1,50) last_name,
SUBSTRB(PARTY.person_first_name,1,40) first_name
FROM hz_cust_account_roles ACCT_ROLE,
hz_parties PARTY,
hz_relationships REL,
hz_cust_accounts ROLE_ACCT
WHERE
ACCT_ROLE.party_id = REL.party_id
AND ACCT_ROLE.role_type = 'CONTACT'
AND REL.subject_id = PARTY.party_id
AND REL.subject_table_name = 'HZ_PARTIES'
AND REL.object_table_name = 'HZ_PARTIES'
AND ACCT_ROLE.cust_account_id = ROLE_ACCT.cust_account_id
AND ROLE_ACCT.party_id = REL.object_id
) invc
, ( SELECT ACCT_ROLE.cust_account_role_id contact_id,
ACCT_ROLE.cust_account_id customer_id,
SUBSTRB(PARTY.person_last_name,1,50) last_name,
SUBSTRB(PARTY.person_first_name,1,40) first_name
FROM hz_cust_account_roles ACCT_ROLE,
hz_parties PARTY,
hz_relationships REL,
hz_cust_accounts ROLE_ACCT
WHERE
ACCT_ROLE.party_id = REL.party_id
AND ACCT_ROLE.role_type = 'CONTACT'
AND REL.subject_id = PARTY.party_id
AND REL.subject_table_name = 'HZ_PARTIES'
AND REL.object_table_name = 'HZ_PARTIES'
AND ACCT_ROLE.cust_account_id = ROLE_ACCT.cust_account_id
AND ROLE_ACCT.party_id = REL.object_id
) shiptoc,
--
-- Modification End for Bug # - 4418524
--
PA_PROJECTS pp,
--ORG_FREIGHT_VL ofv,
RA_SALESREPS ras,
OE_SETS,
AR_LOOKUPS,
MTL_PARAMETERS ship_from_org
WHERE oline.line_id = p_line_id
AND oline.sold_to_org_id = oline_rc.customer_id(+)
AND oline.deliver_to_contact_id = dcontact.contact_id(+)
AND oline.ship_to_contact_id = shiptoc.contact_id(+)
AND oline.invoice_to_contact_id = invc.contact_id(+)
AND oline.intmed_ship_to_contact_id = isc.contact_id(+)
AND oline.salesrep_id = ras.salesrep_id(+)
AND oline.ship_set_id = oe_sets.set_id(+)
AND oline.ship_to_contact_id = rcship.contact_id(+)
AND oline.shipping_method_code = osmv.lookup_code(+)
AND oline.tax_exempt_reason_code = ar_lookups.lookup_code(+)
and ar_lookups.lookup_type(+) = 'TAX_REASON'
AND oline.project_id =pp.project_id(+)
AND oline.ship_from_org_id = ship_from_org.organization_id(+);
SELECT
loc.address_line_1,
loc.address_line_2,
loc.address_line_3,
decode(LOC.CITY,null, null, LOC.CITY|| ', ')
||decode(LOC.state, null, null, LOC.state || ', ') ||
decode(LOC.postal_code,null, null, LOC.postal_code || ', ') ||
decode(LOC.country, null, null, LOC.country) address_line_4 ,
-- loc.address_line_4 address_line_5,
loc.country,
loc.postal_code,
loc.county,
loc.state,
loc.province,
loc.city ,
loc.telephone_number_1
--loc.description
FROM (
SELECT loc.location_id location_id,
loc.address_line_1 address_line_1,
loc.address_line_2 address_line_2,loc.address_line_3 address_line_3,
loc.loc_information13 address_line_4,
loc.town_or_city city,loc.postal_code postal_code,
loc.region_2 state,loc.region_1 county,
loc.country country,loc.region_3 province,
loc.location_code location_code,loc.description description,
loc.telephone_number_1
FROM hr_locations_all loc
UNION ALL
SELECT hz.location_id location_id,
hz.address1 address_line_1,
hz.address2 address_line_2,hz.address3 address_line_3,
hz.address4 address_line_4,
hz.city city,hz.postal_code postal_code,
hz.state state,hz.county county,
hz.country country,hz.province province,
hz.description location_code, hz.description description,
NULL telephone_number_1
FROM hz_locations hz
) LOC
WHERE location_id = c_location_id;
SELECT /*+ INDEX(ACCT_SITE,HZ_CUST_ACCT_SITES_N2) */
party_site.location_id,
site.location location_code,
org.organization_code,
org.organization_name
--cust_acct.account_number customer_Number,
--cust_acct.customer_type,
--party.party_name customer_name
--site.site_use_code,
--site.site_use_id,
--site.org_Id organization_id,
--cust_acct.cust_account_id customer_id
FROM
HZ_CUST_ACCT_SITES_ALL ACCT_SITE,
HZ_PARTY_SITES PARTY_SITE,
HZ_CUST_SITE_USES_ALL SITE,
HZ_PARTIES PARTY,
HZ_CUST_ACCOUNTS CUST_ACCT,
ORG_ORGANIZATION_DEFINITIONS ORG
WHERE SITE.ORG_ID = ORG.ORGANIZATION_ID
AND SITE.CUST_ACCT_SITE_ID = ACCT_SITE.CUST_ACCT_SITE_ID
AND ACCT_SITE.PARTY_SITE_ID = PARTY_SITE.PARTY_SITE_ID
and acct_site.status='A'
AND ACCT_SITE.CUST_ACCOUNT_ID=CUST_ACCT.CUST_ACCOUNT_ID
AND CUST_ACCT.PARTY_ID=PARTY.PARTY_ID
AND CUST_ACCT.status='A'
AND site.status='A'
AND SITE.SITE_USE_CODE = c_site_use_code
AND CUST_ACCT.CUST_ACCOUNT_ID = c_customer_id
AND SITE.SITE_USE_ID = c_site_use_id;
SELECT hou.location_id,
mp.organization_code
-- hou.name organization_name,
-- hou.organization_id
FROM hr_organization_units hou,
mtl_parameters mp,
hr_organization_information hoi1
WHERE hou.ORGANIZATION_ID = mp.ORGANIZATION_ID
AND hou.ORGANIZATION_ID = hoi1.ORGANIZATION_id
AND hoi1.ORG_INFORMATION1= 'INV'
AND hoi1.ORG_INFORMATION2 = 'Y'
AND mp.organization_id = c_organization_id;
SELECT loc.location_id location_id,
loc.address_line_1 address_line_1,
loc.address_line_2 address_line_2,loc.address_line_3 address_line_3,
loc.loc_information13 address_line_4,
loc.town_or_city city,loc.postal_code postal_code,
loc.region_2 state,loc.region_1 county,
loc.country country,loc.region_3 province,
loc.location_code location_code,loc.description location_description
FROM hr_locations_all loc
WHERE loc.location_id = c_location_id
UNION ALL
SELECT hz.location_id location_id,
hz.address1 address_line_1,
hz.address2 address_line_2,hz.address3 address_line_3,
hz.address4 address_line_4,
hz.city city,hz.postal_code postal_code,
hz.state state,hz.county county,
hz.country country,hz.province province,
hz.description location_code, hz.description location_description
FROM hz_locations hz
WHERE hz.location_id = c_location_id;
SELECT
oline.booked_flag,
oline.cancelled_flag,
oline.component_code,
oline.cust_po_number,
to_char(oline.earliest_acceptable_date, G_DATE_FORMAT_MASK),
to_char(oline.explosion_date, G_DATE_FORMAT_MASK),
oline.freight_carrier_code,
to_char(oline.latest_acceptable_date, G_DATE_FORMAT_MASK),
Oline.open_flag,
to_char(oline.actual_shipment_date, G_DATE_FORMAT_MASK),
oline.created_by,
oline.last_updated_by,
to_char(oline.last_update_date, G_DATE_FORMAT_MASK),
oline.attribute1,
oline.attribute2,
oline.attribute3,
oline.attribute4,
oline.attribute5,
oline.attribute6,
oline.attribute7,
oline.attribute8,
oline.attribute9,
oline.attribute10,
oline.attribute11,
oline.attribute12,
oline.attribute13,
oline.attribute14,
oline.attribute15,
oline.return_attribute1,
oline.return_attribute2,
oline.return_attribute3,
oline.return_attribute4,
oline.return_attribute5,
oline.return_attribute6,
oline.return_attribute7,
oline.return_attribute8,
oline.return_attribute9,
oline.return_attribute10,
oline.return_attribute11,
oline.return_attribute12,
oline.return_attribute13,
oline.return_attribute14,
oline.return_attribute15,
oline.return_context,
oline.context,
to_char(oline.creation_date, G_DATE_FORMAT_MASK),
oline.fulfilled_quantity,
oline.ordered_item,
oline.line_number,
oline.ordered_quantity,
to_char(oline.promise_date, G_DATE_FORMAT_MASK),
oline.order_quantity_uom,
to_char(oline.request_date, G_DATE_FORMAT_MASK),
to_char(oline.schedule_ship_date, G_DATE_FORMAT_MASK),
oline.shipped_quantity,
oline.shipping_quantity,
oline.shipping_quantity_uom,
oline.over_ship_reason_code,
oline.packing_instructions,
pp.name project_name,
ras.name salesreps_name,
to_char(oline.schedule_arrival_date, G_DATE_FORMAT_MASK),
oe_sets.set_name ship_set_name,
osmv.meaning shipping_method_name,
ar_lookups.meaning tax_exempt_reason,
oline.tax_code,
oline.tax_exempt_flag,
oline.tax_exempt_number,
oline.tax_rate,
oline.shipment_number,
oline.shipping_instructions,
--rcship.last_name,
LTRIM(rcship.last_name ||decode(rcship.first_name,NULL,NULL,','||rcship.first_name)) ship_to_contact_name,
LTRIM(isc.last_name || decode(isc.first_name,NULL,NULL,','|| isc.first_name)) intmed_ship_to_contact_name,
LTRIM(invc.last_name || decode(invc.first_name,NULL,NULL,','|| invc.first_name)) invoice_to_contact_name,
LTRIM(dcontact.last_name || decode(dcontact.first_name,NULL,NULL,','|| dcontact.first_name)) deliver_to_contact_name,
oline.sold_from_org_id,
oline.sold_to_org_id,
oline.deliver_to_org_id,
oline.ship_to_org_id,
oline.invoice_to_org_id,
ship_from_org.organization_code,
oline.header_id
FROM OE_ORDER_LINES_ALL oline,
OE_SHIP_METHODS_V osmv
--
-- Modification Start for Bug # - 4418524
--
-- As part of TCA related changes ra_customers, ra_contacts views are
-- obsoleted in R12. The columns fetched from these views are fetched
-- from "HZ_PARTIES", "HZ_CUST_ACCOUNTS", "HZ_CUST_ACCOUNT_ROLES",
-- "HZ_CUST_ACCOUNTS", "HZ_RELATIONSHIPS".
--
-- Following six table alias are commented
--, ra_contacts rcship
--, ra_contacts dcontact
--, ra_contacts isc
--, ra_contacts invc
--
-- Following 4 Queries are added to replace the above commented
-- views
--
, ( SELECT ACCT_ROLE.cust_account_role_id contact_id,
ACCT_ROLE.cust_account_id customer_id,
SUBSTRB(PARTY.person_last_name,1,50) last_name,
SUBSTRB(PARTY.person_first_name,1,40) first_name
FROM hz_cust_account_roles ACCT_ROLE,
hz_parties PARTY,
hz_relationships REL,
hz_cust_accounts ROLE_ACCT
WHERE
ACCT_ROLE.party_id = REL.party_id
AND ACCT_ROLE.role_type = 'CONTACT'
AND REL.subject_id = PARTY.party_id
AND REL.subject_table_name = 'HZ_PARTIES'
AND REL.object_table_name = 'HZ_PARTIES'
AND ACCT_ROLE.cust_account_id = ROLE_ACCT.cust_account_id
AND ROLE_ACCT.party_id = REL.object_id
) rcship
, ( SELECT ACCT_ROLE.cust_account_role_id contact_id,
ACCT_ROLE.cust_account_id customer_id,
SUBSTRB(PARTY.person_last_name,1,50) last_name,
SUBSTRB(PARTY.person_first_name,1,40) first_name
FROM hz_cust_account_roles ACCT_ROLE,
hz_parties PARTY,
hz_relationships REL,
hz_cust_accounts ROLE_ACCT
WHERE
ACCT_ROLE.party_id = REL.party_id
AND ACCT_ROLE.role_type = 'CONTACT'
AND REL.subject_id = PARTY.party_id
AND REL.subject_table_name = 'HZ_PARTIES'
AND REL.object_table_name = 'HZ_PARTIES'
AND ACCT_ROLE.cust_account_id = ROLE_ACCT.cust_account_id
AND ROLE_ACCT.party_id = REL.object_id
) dcontact
, ( SELECT ACCT_ROLE.cust_account_role_id contact_id,
ACCT_ROLE.cust_account_id customer_id,
SUBSTRB(PARTY.person_last_name,1,50) last_name,
SUBSTRB(PARTY.person_first_name,1,40) first_name
FROM hz_cust_account_roles ACCT_ROLE,
hz_parties PARTY,
hz_relationships REL,
hz_cust_accounts ROLE_ACCT
WHERE
ACCT_ROLE.party_id = REL.party_id
AND ACCT_ROLE.role_type = 'CONTACT'
AND REL.subject_id = PARTY.party_id
AND REL.subject_table_name = 'HZ_PARTIES'
AND REL.object_table_name = 'HZ_PARTIES'
AND ACCT_ROLE.cust_account_id = ROLE_ACCT.cust_account_id
AND ROLE_ACCT.party_id = REL.object_id
) isc
, ( SELECT ACCT_ROLE.cust_account_role_id contact_id,
ACCT_ROLE.cust_account_id customer_id,
SUBSTRB(PARTY.person_last_name,1,50) last_name,
SUBSTRB(PARTY.person_first_name,1,40) first_name
FROM hz_cust_account_roles ACCT_ROLE,
hz_parties PARTY,
hz_relationships REL,
hz_cust_accounts ROLE_ACCT
WHERE
ACCT_ROLE.party_id = REL.party_id
AND ACCT_ROLE.role_type = 'CONTACT'
AND REL.subject_id = PARTY.party_id
AND REL.subject_table_name = 'HZ_PARTIES'
AND REL.object_table_name = 'HZ_PARTIES'
AND ACCT_ROLE.cust_account_id = ROLE_ACCT.cust_account_id
AND ROLE_ACCT.party_id = REL.object_id
) invc,
PA_PROJECTS pp,
--ORG_FREIGHT_VL ofv,
RA_SALESREPS ras,
OE_SETS,
AR_LOOKUPS,
MTL_PARAMETERS ship_from_org
WHERE oline.line_id = p_line_id
AND oline.deliver_to_contact_id = dcontact.contact_id(+)
AND oline.intmed_ship_to_contact_id = isc.contact_id(+)
AND oline.salesrep_id = ras.salesrep_id(+)
AND oline.ship_set_id = oe_sets.set_id(+)
AND oline.ship_to_contact_id = rcship.contact_id(+)
AND oline.shipping_method_code = osmv.lookup_code(+)
AND oline.tax_exempt_reason_code = ar_lookups.lookup_code(+)
and ar_lookups.lookup_type(+) = 'TAX_REASON'
AND oline.invoice_to_contact_id = invc.contact_id(+)
AND oline.project_id =pp.project_id(+)
AND oline.ship_from_org_id = ship_from_org.organization_id(+);
SELECT mmtt.inventory_item_id,
mmtt.organization_id,
NVL(mmtt.subinventory_code,
wfs.completion_subinventory) subinventory_code,
NVL(mmtt.locator_id, wfs.completion_locator_id) locator_id,
NVL(mtlt.lot_number,mmtt.lot_number) lot_number ,
mmtt.serial_number,
NULL serial_number_start,
NULL serial_number_end,
mmtt.cost_group_id ,
NVL(mmtt.project_id , wfs.project_id) project_id ,
NVL(mmtt.task_id , wfs.task_id) task_id ,
mmtt.transaction_quantity quantity,
mmtt.transaction_uom uom,
mmtt.revision revision,
NVL(mmtt.alternate_bom_designator,
wfs.alternate_bom_designator) alternate_bom_designator,
NVL(mmtt.alternate_routing_designator,
wfs.alternate_routing_designator) alternate_routing_designator,
NVL(mmtt.demand_source_header_id,
wfs.demand_source_header_id) sale_header_id,
NVL(mmtt.demand_source_line, wfs.demand_source_line) sale_line_id,
NVL(mmtt.kanban_card_id,wfs.kanban_card_id) kanban_card_id,
NVL(NVL(mmtt.transfer_lpn_id, mmtt.content_lpn_id), mmtt.lpn_id) lpn_id,
mmtt.transaction_source_id wip_entity_id,
NVL(mmtt.schedule_number, wfs.schedule_number) schedule_number,
msi.lot_control_code,
msi.serial_number_control_code serial_number_control_code,
mmtt.transaction_temp_id transaction_id
FROM MTL_MATERIAL_TRANSACTIONS_TEMP mmtt,
MTL_TRANSACTION_LOTS_TEMP mtlt,
MTL_SYSTEM_ITEMS msi,
WIP_ENTITIES WE,
WIP_FLOW_SCHEDULES wfs
WHERE mmtt.transaction_temp_id = l_transaction_id
AND mmtt.transaction_temp_id = mtlt.transaction_temp_id(+)
AND mmtt.organization_id = msi.organization_id
AND mmtt.inventory_item_id = msi.inventory_item_id
AND mmtt.transaction_source_id = wfs.wip_entity_id(+)
AND mmtt.transaction_source_id = we.wip_entity_id(+)
AND we.entity_type(+) = 4; -- Flow
SELECT mmtt.inventory_item_id,
mmtt.organization_id,
NVL(mmtt.subinventory_code,
wfs.completion_subinventory) subinventory_code,
NVL(mmtt.locator_id, wfs.completion_locator_id) locator_id,
NVL(mtlt.lot_number,mmtt.lot_number) lot_number ,
mmtt.serial_number,
NULL serial_number_start,
NULL serial_number_end,
mmtt.cost_group_id ,
NVL(mmtt.project_id , wfs.project_id) project_id ,
NVL(mmtt.task_id , wfs.task_id) task_id ,
mmtt.transaction_quantity quantity,
mmtt.transaction_uom uom,
mmtt.revision revision,
NVL(mmtt.alternate_bom_designator,
wfs.alternate_bom_designator) alternate_bom_designator,
NVL(mmtt.alternate_routing_designator,
wfs.alternate_routing_designator) alternate_routing_designator,
NVL(mmtt.demand_source_header_id,
wfs.demand_source_header_id) sale_header_id,
NVL(mmtt.demand_source_line, wfs.demand_source_line) sale_line_id,
NVL(mmtt.kanban_card_id,wfs.kanban_card_id) kanban_card_id,
NVL(NVL(mmtt.transfer_lpn_id, mmtt.content_lpn_id), mmtt.lpn_id) lpn_id,
mmtt.transaction_source_id wip_entity_id,
NVL(mmtt.schedule_number, wfs.schedule_number) schedule_number,
msi.lot_control_code,
msi.serial_number_control_code serial_number_control_code,
mmtt.transaction_temp_id transaction_id
FROM MTL_MATERIAL_TRANSACTIONS_TEMP mmtt,
MTL_TRANSACTION_LOTS_TEMP mtlt,
MTL_SYSTEM_ITEMS msi,
WIP_ENTITIES WE,
WIP_FLOW_SCHEDULES wfs,
MTL_MATERIAL_TRANSACTIONS_TEMP mmtt_orig
WHERE mmtt.transaction_temp_id = mtlt.transaction_temp_id(+)
AND mmtt.organization_id = msi.organization_id
AND mmtt.inventory_item_id = msi.inventory_item_id
AND mmtt.transaction_source_id = wfs.wip_entity_id(+)
AND mmtt.transaction_source_id = we.wip_entity_id(+)
AND we.entity_type(+) = 4
AND mmtt.transaction_header_id = mmtt_orig.transaction_header_id
AND mmtt.transaction_temp_id <>mmtt_orig.transaction_temp_id
AND mmtt_orig.content_lpn_id IS NOT NULL
AND mmtt_orig.transaction_temp_id = l_transaction_id;
SELECT mti.inventory_item_id,
mti.organization_id,
NVL(mti.subinventory_code,
wfs.completion_subinventory) subinventory_code,
NVL(mti.locator_id, wfs.completion_locator_id) locator_id,
-- mti.source_lot_number lot_number, -- Commented for Bug 2894995 : joabraha
mtli.lot_number lot_number, -- Added for Bug 2894995 : joabraha
NULL serial_number,
NULL serial_number_start,
NULL serial_number_end,
mti.cost_group_id,
NVL(mti.project_id , wfs.project_id) project_id ,
NVL(mti.task_id , wfs.task_id) task_id ,
mti.transaction_quantity quantity,
mti.transaction_uom uom,
mti.revision revision,
NVL(mti.alternate_bom_designator,
wfs.alternate_bom_designator) alternate_bom_designator,
NVL(mti.alternate_routing_designator,
wfs.alternate_routing_designator) alternate_routing_designator,
NVL(mti.demand_source_header_id,
wfs.demand_source_header_id) sale_header_id,
NVL(mti.demand_source_line, wfs.demand_source_line) sale_line_id,
NVL(mti.kanban_card_id,wfs.kanban_card_id) kanban_card_id,
mti.transfer_lpn_id lpn_id ,
mti.transaction_source_id wip_entity_id,
NVL(mti.schedule_number, wfs.schedule_number) schedule_number,
msi.lot_control_code,
msi.serial_number_control_code,
mti.transaction_interface_id transaction_id
FROM MTL_TRANSACTIONS_INTERFACE mti,
-- MTL_TRANSACTION_LOTS_INTERFACE mtli, -- Bug 2576424 : joabraha
-- Bug 2904877, change back to using MTLI,
-- this depends on WIP's fix on bug 2904857
MTL_TRANSACTION_LOTS_INTERFACE mtli,
--MTL_TRANSACTION_LOTS_TEMP mtlt,
MTL_SYSTEM_ITEMS msi,
WIP_FLOW_SCHEDULES wfs
WHERE mti.transaction_interface_id = l_transaction_id
-- AND mti.transaction_interface_id = mtli.transaction_interface_id(+) -- Bug 2576424 : joabraha
-- AND mti.transaction_interface_id = mtlt.transaction_temp_id(+) -- Bug 2576424 : joabraha
-- Bug 2904877, change back to using MTLI,
-- this depends on WIP's fix on bug 2904857
AND mti.transaction_interface_id = mtli.transaction_interface_id(+)
AND mti.organization_id = msi.organization_id
AND mti.inventory_item_id = msi.inventory_item_id
AND mti.transaction_source_id = wfs.wip_entity_id(+);
SELECT mtrl.inventory_item_id,
mtrl.organization_id,
NVL(mmtt.subinventory_code, mtrl.to_subinventory_code) subinventory_code,
mtrl.to_locator_id locator_id,
NVL(mmtt.lot_number,mtrl.lot_number) lot_number,
NULL serial_number,
NVL(mtrl.serial_number_start,'@@') serial_number_start,
NVL(mtrl.serial_number_end,'@@') serial_number_end,
mtrl.to_cost_group_id cost_group_id,
mtrl.project_id ,
mtrl.task_id ,
mtrl.quantity quantity,
mtrl.uom_code uom,
mtrl.revision revision,
mmtt.alternate_bom_designator alternate_bom_designator,
mmtt.alternate_routing_designator alternate_routing_designator,
mmtt.demand_source_header_id sale_header_id,
NVL(mmtt.demand_source_line, mtrl.txn_source_line_id) sale_line_id,
mmtt.kanban_card_id kanban_card_id,
mtrl.lpn_id lpn_id ,
mtrl.txn_source_id wip_entity_id,
mmtt.schedule_number schedule_number,
msi.lot_control_code,
msi.serial_number_control_code,
mmtt.transaction_temp_id transaction_id
FROM MTL_TXN_REQUEST_LINES mtrl,
MTL_MATERIAL_TRANSACTIONS_TEMP mmtt,
MTL_TRANSACTION_LOTS_TEMP mtlt,
MTL_SYSTEM_ITEMS msi,
WIP_ENTITIES WE
WHERE mtrl.line_id = l_transaction_id
AND mtrl.line_id = mmtt.move_order_line_id(+)
AND mmtt.transaction_temp_id = mtlt.transaction_temp_id(+)
AND mtrl.organization_id = msi.organization_id
AND mtrl.inventory_item_id = msi.inventory_item_id
AND mtrl.txn_source_id = we.wip_entity_id(+)
AND we.entity_type(+) = 4;
SELECT wfs.primary_item_id inventory_item_id,
wfs.organization_id organization_id,
NVL(mmtt.subinventory_code, wfs.completion_subinventory) subinventory_code,
NVL(mmtt.locator_id, wfs.completion_locator_id) locator_id,
NULL lot_number ,
NULL serial_number,
NULL serial_number_start,
NULL serial_number_end,
mmtt.cost_group_id cost_group_id,
NVL(mmtt.project_id, wfs.project_id) project_id ,
NVL(mmtt.task_id, wfs.task_id) task_id ,
NVL(mmtt.transaction_quantity, wfs.quantity_completed) quantity,
mmtt.transaction_uom uom,
mmtt.revision revision,
NVL(mmtt.alternate_bom_designator,wfs.alternate_bom_designator) alternate_bom_designator,
NVL(mmtt.alternate_routing_designator, wfs.alternate_routing_designator) alternate_routing_designator,
NVL(mmtt.demand_source_header_id,
wfs.demand_source_header_id ) sale_header_id,
NVL(mmtt.demand_source_line,
wfs.demand_source_line) sale_line_id,
NVL(mmtt.kanban_card_id,wfs.kanban_card_id) kanban_card_id,
NVL(NVL(mmtt.transfer_lpn_id, mmtt.content_lpn_id), mmtt.lpn_id) lpn_id,
wfs.wip_entity_id wip_entity_id,
wfs.schedule_number schedule_number,
msi.lot_control_code,
msi.serial_number_control_code,
mmtt.transaction_temp_id transaction_id
FROM WIP_FLOW_SCHEDULES wfs,
MTL_MATERIAL_TRANSACTIONS_TEMP mmtt,
MTL_TRANSACTION_LOTS_TEMP mtlt,
MTL_SYSTEM_ITEMS msi,
WIP_ENTITIES WE
WHERE mmtt.transaction_source_type_id = 5
AND mmtt.transaction_action_id = 31
AND mmtt.organization_id = wfs.organization_id
AND mmtt.inventory_item_id = wfs.primary_item_id
AND mmtt.transaction_source_id = wfs.wip_entity_id
AND mmtt.transaction_temp_id = mtlt.transaction_temp_id(+)
AND wfs.organization_id = msi.organization_id
AND wfs.primary_item_id = msi.inventory_item_id
AND wfs.wip_entity_id = we.wip_entity_id
AND we.entity_type = 4 -- Flow
AND wfs.wip_entity_id = l_transaction_id;
SELECT wfs.primary_item_id inventory_item_id,
wfs.organization_id organization_id,
NVL(mmt.subinventory_code, wfs.completion_subinventory) subinventory_code,
NVL(mmt.locator_id, wfs.completion_locator_id) locator_id,
mtln.lot_number lot_number ,
NULL serial_number,
NULL serial_number_start,
NULL serial_number_end,
mmt.cost_group_id cost_group_id,
NVL(mmt.project_id, wfs.project_id) project_id ,
NVL(mmt.task_id, wfs.task_id) task_id ,
NVL(mmt.transaction_quantity, wfs.quantity_completed) quantity,
mmt.transaction_uom uom,
mmt.revision revision,
wfs.alternate_bom_designator alternate_bom_designator,
wfs.alternate_routing_designator alternate_routing_designator,
wfs.demand_source_header_id sale_header_id,
wfs.demand_source_line sale_line_id,
wfs.kanban_card_id kanban_card_id,
NVL(NVL(mmt.transfer_lpn_id, mmt.content_lpn_id), mmt.lpn_id) lpn_id ,
wfs.wip_entity_id wip_entity_id,
wfs.schedule_number schedule_number,
msi.lot_control_code,
msi.serial_number_control_code,
mmt.transaction_id transaction_id
FROM wip_flow_schedules wfs,
mtl_material_transactions mmt,
mtl_transaction_lot_numbers mtln,
wip_entities we,
mtl_system_items msi
WHERE mmt.transaction_source_type_id = 5
AND mmt.transaction_action_id = 31
AND mmt.organization_id = wfs.organization_id
AND mmt.inventory_item_id = wfs.primary_item_id
AND mmt.transaction_source_id = wfs.wip_entity_id
AND wfs.organization_id = msi.organization_id
AND wfs.primary_item_id = msi.inventory_item_id
AND we.wip_entity_id = wfs.wip_entity_id
AND we.entity_type = 4
AND mmt.transaction_id = mtln.transaction_id(+)
AND wfs.wip_entity_id = l_transaction_id;
SELECT wfs.primary_item_id inventory_item_id,
wfs.organization_id organization_id,
wfs.completion_subinventory subinventory_code,
wfs.completion_locator_id locator_id,
NULL lot_number ,
NULL serial_number,
NULL serial_number_start,
NULL serial_number_end,
NULL cost_group_id,
wfs.project_id project_id ,
wfs.task_id task_id ,
wfs.quantity_completed quantity,
msi.primary_uom_code uom,
NULL revision,
wfs.alternate_bom_designator alternate_bom_designator,
wfs.alternate_routing_designator alternate_routing_designator,
wfs.demand_source_header_id sale_header_id,
wfs.demand_source_line sale_line_id,
wfs.kanban_card_id kanban_card_id,
NULL lpn_id ,
wfs.wip_entity_id wip_entity_id,
wfs.schedule_number schedule_number,
msi.lot_control_code,
msi.serial_number_control_code,
NULL transaction_id
FROM wip_flow_schedules wfs,
wip_entities we,
mtl_system_items msi
WHERE wfs.organization_id = msi.organization_id
AND wfs.primary_item_id = msi.inventory_item_id
AND we.wip_entity_id = wfs.wip_entity_id
AND we.entity_type = 4
AND wfs.wip_entity_id = l_transaction_id;
SELECT msn.serial_number
FROM mtl_material_transactions_temp mmtt,
mtl_serial_numbers_temp msnt, mtl_serial_numbers msn
where mmtt.transaction_temp_id = msnt.transaction_temp_id
and msnt.fm_serial_number <= msn.serial_number AND
msnt.to_serial_number >= msn.serial_number
and mmtt.organization_id = msn.current_organization_id
and mmtt.inventory_item_id = msn.inventory_item_id
and mmtt.transaction_temp_id = l_transaction_id
UNION
-- Lot and Serial Control
SELECT msn.serial_number
FROM mtl_material_transactions_temp mmtt, mtl_transaction_lots_temp mtlt,
mtl_serial_numbers_temp msnt, mtl_serial_numbers msn
where mmtt.transaction_temp_id = mtlt.transaction_temp_id
and mtlt.serial_transaction_temp_id = msnt.transaction_temp_id
and msnt.fm_serial_number <= msn.serial_number AND
msnt.to_serial_number >= msn.serial_number
and mmtt.organization_id = msn.current_organization_id
and mmtt.inventory_item_id = msn.inventory_item_id
and mmtt.transaction_temp_id = l_transaction_id
-- Bug 2882958, added parameter p_lot_number to restrict on lot
and mtlt.lot_number = p_lot_number; */
SELECT msn.serial_number
FROM mtl_material_transactions_temp mmtt,
mtl_serial_numbers msn
where mmtt.transaction_temp_id = l_transaction_id
and (mmtt.lpn_id = msn.lpn_id
or mmtt.content_lpn_id = msn.lpn_id) -- Modified for the bug # 5740354
and mmtt.inventory_item_id = msn.inventory_item_id
UNION
SELECT msn.serial_number
FROM mtl_material_transactions_temp mmtt,
mtl_serial_numbers msn
where mmtt.transaction_temp_id = l_transaction_id
and (mmtt.lpn_id = msn.lpn_id
or mmtt.content_lpn_id = msn.lpn_id) -- Modified for the bug # 5740354
and mmtt.inventory_item_id = msn.inventory_item_id
and msn.lot_number = p_lot_number;
SELECT msn.serial_number
FROM mtl_transactions_interface mti,
mtl_serial_numbers_interface msni, mtl_serial_numbers msn
where mti.transaction_interface_id =msni.transaction_interface_id
and msni.fm_serial_number <= msn.serial_number AND
msni.to_serial_number >= msn.serial_number
and mti.organization_id = msn.current_organization_id
and mti.inventory_item_id = msn.inventory_item_id
and mti.transaction_interface_id = l_transaction_id
UNION
-- Lot and Serial Control
SELECT msn.serial_number
FROM mtl_transactions_interface mti, mtl_transaction_lots_interface mtli,
mtl_serial_numbers_interface msni, mtl_serial_numbers msn
where mti.transaction_interface_id = mtli.transaction_interface_id
and mtli.serial_transaction_temp_id = msni.transaction_interface_id
and msni.fm_serial_number <= msn.serial_number AND
msni.to_serial_number >= msn.serial_number
and mti.organization_id = msn.current_organization_id
and mti.inventory_item_id = msn.inventory_item_id
and mti.transaction_interface_id = l_transaction_id
-- Bug 2882958, added parameter p_lot_number to restrict on lot
and mtli.lot_number = p_lot_number;
select serial_number
FROM mtl_serial_numbers
WHERE current_organization_id = l_in_rec.organization_id
AND inventory_item_id = l_in_rec.inventory_item_id
AND current_subinventory_code = l_in_rec.subinventory_code
AND NVL(revision, '@@@@') = NVL(l_in_rec.revision,'@@@@')
AND NVL(lot_number, '@@@@') = NVL(l_in_rec.lot_number, '@@@@')
AND last_transaction_id = l_transaction_id;
l_selected_fields INV_LABEL.label_field_variable_tbl_type;
l_selected_fields_count NUMBER;
trace(' Getting default format selected fields ');
x_variables => l_selected_fields
,x_variables_count => l_selected_fields_count
,p_format_id => p_label_type_info.default_format_id);
IF (l_selected_fields_count=0) OR (l_selected_fields.count =0 ) THEN
IF (l_debug = 1) THEN
trace('no fields defined for this format: ' || p_label_type_info.default_format_id || ',' || p_label_type_info.default_format_name);
trace(' Found variable defined for this format, cont = ' || l_selected_fields_count);
P_LAST_UPDATE_DATE => sysdate,
P_LAST_UPDATED_BY => FND_GLOBAL.user_id,
P_CREATION_DATE => sysdate,
P_CREATED_BY => FND_GLOBAL.user_id,
--P_PRINTER_NAME => l_printer,-- Removed in R12: 4396558
P_SUBINVENTORY_CODE => l_subinventory_code, --Added for bug13781065 Start
P_LOCATOR_ID => l_locator_id, --End bug13781065
P_LPN_ID => l_lpn_id,-- Added for bug 14299696: Populate lpn_id into WMS_LABEL_REQUESTS then Custom fields of flow contentd label can use it.
P_BUSINESS_FLOW_CODE => p_label_type_info.business_flow_code,
x_return_status => l_return_status,
x_label_format_id => l_label_format_id,
x_label_format => l_label_format,
x_label_request_id => l_label_request_id);
x_variables => l_selected_fields
, x_variables_count => l_selected_fields_count
, p_format_id => l_label_format_id);
IF (l_selected_fields_count=0) OR (l_selected_fields.count =0 ) THEN
IF (l_debug = 1) THEN
trace('no fields defined for this format: ' || l_label_format|| ',' ||l_label_format_id);
trace(' Found selected_fields for format ' || l_label_format ||', num='|| l_selected_fields_count);
-- Loop for each selected fields, find the columns and write into the XML_content
FOR i IN 1..l_selected_fields.count LOOP
l_field_id := l_selected_fields(i).label_field_id;
IF (l_selected_fields(i).SQL_STMT IS NOT NULL AND l_selected_fields(i).column_name = 'sql_stmt') THEN
IF (l_debug = 1) THEN
trace('Custom Labels Trace [INVLA10B.pls]: ------------------------- REPORT BEGIN-------------------------------------');
trace('Custom Labels Trace [INVLA10B.pls]: LABEL_FIELD_ID : ' || l_selected_fields(i).label_field_id);
trace('Custom Labels Trace [INVLA10B.pls]: FIELD_VARIABLE_NAME : ' || l_selected_fields(i).variable_name);
trace('Custom Labels Trace [INVLA10B.pls]: COLUMN_NAME : ' || l_selected_fields(i).column_name);
trace('Custom Labels Trace [INVLA10B.pls]: SQL_STMT : ' || l_selected_fields(i).sql_stmt);
l_sql_stmt := l_selected_fields(i).sql_stmt;
|| l_selected_fields(i).variable_name
|| '">'
|| l_sql_stmt_result
|| variable_e;
ELSIF LOWER(l_selected_fields(i).column_name) = 'current_date' THEN
l_content_item_data := l_content_item_data || VARIABLE_B ||
l_selected_fields(i).variable_name ||
'">' || INV_LABEL.G_DATE || VARIABLE_E;
ELSIF LOWER(l_selected_fields(i).column_name) = 'current_time' THEN
l_content_item_data := l_content_item_data || VARIABLE_B ||
l_selected_fields(i).variable_name ||
'">' || INV_LABEL.G_TIME || VARIABLE_E;
ELSIF LOWER(l_selected_fields(i).column_name) = 'request_user' THEN
l_content_item_data := l_content_item_data || VARIABLE_B ||
l_selected_fields(i).variable_name ||
'">' || INV_LABEL.G_USER || VARIABLE_E;
l_selected_fields(i).variable_name || '">' ||
l_field_value ||
VARIABLE_E;