[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT 'N',
ril.schedule_item_num,
null,
null,
null,
null,
null,
null
FROM rlm_interface_headers rih,
rlm_interface_lines_all ril
WHERE ril.header_id = rih.header_id
AND rih.header_id = x_header_id
AND rih.org_id = ril.org_id
GROUP BY schedule_item_num;
SELECT 'S',
null,
ril.cust_ship_from_org_ext,
ril.cust_ship_to_ext,
ril.customer_item_ext,
null,
null,
null
FROM rlm_interface_headers rih,
rlm_interface_lines_all ril
WHERE ril.header_id = rih.header_id
AND rih.header_id = x_header_id
AND rih.org_id = ril.org_id
GROUP BY cust_ship_from_org_ext, cust_ship_to_ext,
customer_item_ext;
SELECT 'S',
null,
null,
null,
null,
ril.customer_item_id,
ril.ship_from_org_id,
ril.ship_to_address_id
FROM rlm_interface_headers rih,
rlm_interface_lines_all ril
WHERE ril.header_id = rih.header_id
AND rih.header_id = x_header_id
AND rih.org_id = ril.org_id
GROUP BY customer_item_id,
ship_from_org_id,
ship_to_address_id;
SELECT DISTINCT ACCT_SITE.CUST_ACCOUNT_ID
INTO v_shipToCustomerId
FROM HZ_CUST_ACCT_SITES ACCT_SITE ,
ECE_TP_HEADERS eth
WHERE ACCT_SITE.tp_header_id = eth.tp_header_id
AND ACCT_SITE.ece_tp_location_code = v_Group_rec.cust_ship_to_ext
AND eth.TP_REFERENCE_EXT1 = v_Customer;
RLM_TPA_SV.UpdateInterfaceLines(g_header_rec);
g_lines_tab.DELETE;
/* update error status for the entire group */
RLM_TPA_SV.UpdateInterfaceLines(g_header_rec);
RLM_TPA_SV.UpdateInterfaceLines(g_header_rec);
/* update error status for the entire group */
RLM_TPA_SV.UpdateInterfaceLines(g_header_rec);
UpdateInterfaceHeaders;
UpdateInterfaceHeaders;
UpdateInterfaceHeaders;
UpdateInterfaceHeaders;
UpdateInterfaceHeaders;
SELECT order_number
INTO v_ord_num
FROM oe_order_headers_all
WHERE header_id = x_order_header_id;
SELECT open_flag, order_number
INTO v_OpenFlag, v_OrderNumber
FROM oe_order_headers_all
WHERE header_id = x_lines_rec.Order_header_id;
SELECT order_number, open_flag
INTO v_Ordernumber, v_OpenFlag
FROM oe_order_headers_all
WHERE header_id = x_setup_terms_rec.header_id;
SELECT 1
FROM MTL_UNITS_OF_MEASURE_vl
WHERE UOM_CODE = x_lines_rec.uom_code;
SELECT PRIMARY_UOM_CODE
FROM MTL_SYSTEM_ITEMS
WHERE INVENTORY_ITEM_ID = x_lines_rec.INVENTORY_ITEM_ID
AND ORGANIZATION_ID = x_lines_rec.ship_from_org_id;
SELECT Count(*)
INTO v_Count
FROM MTL_UOM_CONVERSIONS a, MTL_UOM_CONVERSIONS b
WHERE a.UOM_CODE = v_Primary_UOM_Code
AND b.UOM_CODE = x_lines_rec.UOM_Code
AND a.UOM_CLASS = b.UOM_CLASS ;
SELECT DECODE(INSTR(x_match_key,'A'),0,'N','Y'),
DECODE(INSTR(x_match_key,'B'),0,'N','Y'),
DECODE(INSTR(x_match_key,'C'),0,'N','Y'),
DECODE(INSTR(x_match_key,'D'),0,'N','Y'),
DECODE(INSTR(x_match_key,'E'),0,'N','Y'),
DECODE(INSTR(x_match_key,'F'),0,'N','Y'),
DECODE(INSTR(x_match_key,'G'),0,'N','Y'),
DECODE(INSTR(x_match_key,'H'),0,'N','Y'),
DECODE(INSTR(x_match_key,'I'),0,'N','Y'),
DECODE(INSTR(x_match_key,'J'),0,'N','Y'),
DECODE(INSTR(x_match_key,'K'),0,'N','Y'),
DECODE(INSTR(x_match_key,'L'),0,'N','Y'),
DECODE(INSTR(x_match_key,'M'),0,'N','Y'),
DECODE(INSTR(x_match_key,'N'),0,'N','Y'),
DECODE(INSTR(x_match_key,'O'),0,'N','Y'),
DECODE(INSTR(x_match_key,'P'),0,'N','Y'),
DECODE(INSTR(x_match_key,'Q'),0,'N','Y'),
DECODE(INSTR(x_match_key,'R'),0,'N','Y'),
DECODE(INSTR(x_match_key,'S'),0,'N','Y'),
DECODE(INSTR(x_match_key,'T'),0,'N','Y'),
DECODE(INSTR(x_match_key,'U'),0,'N','Y'),
DECODE(INSTR(x_match_key,'V'),0,'N','Y'),
DECODE(INSTR(x_match_key,'W'),0,'N','Y'),
DECODE(INSTR(x_match_key,'X'),0,'N','Y'),
DECODE(INSTR(x_match_key,'Y'),0,'N','Y'),
DECODE(INSTR(x_match_key,'Z'),0,'N','Y'),
DECODE(INSTR(x_match_key,'1'),0,'N','Y'),
DECODE(INSTR(x_match_key,'2'),0,'N','Y'),
DECODE(INSTR(x_match_key,'3'),0,'N','Y'),
DECODE(INSTR(x_match_key,'4'),0,'N','Y'),
DECODE(INSTR(x_match_key,'5'),0,'N','Y'),
DECODE(INSTR(x_match_key,'6'),0,'N','Y'),
DECODE(INSTR(x_match_key,'7'),0,'N','Y'),
DECODE(INSTR(x_match_key,'8'),0,'N','Y'),
DECODE(INSTR(x_match_key,'9'),0,'N','Y')
INTO
x_match_rec.cust_production_line,
x_match_rec.customer_dock_code,
x_match_rec.request_date,
x_match_rec.schedule_date,
x_match_rec.cust_po_number,
x_match_rec.customer_item_revision,
x_match_rec.customer_job,
x_match_rec.cust_model_serial_number,
x_match_rec.industry_attribute1,
x_match_rec.industry_attribute2,
x_match_rec.industry_attribute4,
x_match_rec.industry_attribute5,
x_match_rec.industry_attribute6,
x_match_rec.industry_attribute9,
x_match_rec.industry_attribute10,
x_match_rec.industry_attribute11,
x_match_rec.industry_attribute12,
x_match_rec.industry_attribute13,
x_match_rec.industry_attribute14,
x_match_rec.industry_attribute15,
x_match_rec.attribute1,
x_match_rec.attribute2,
x_match_rec.attribute3,
x_match_rec.attribute4,
x_match_rec.attribute5,
x_match_rec.attribute6,
x_match_rec.attribute7,
x_match_rec.attribute8,
x_match_rec.attribute9,
x_match_rec.attribute10,
x_match_rec.attribute11,
x_match_rec.attribute12,
x_match_rec.attribute13,
x_match_rec.attribute14,
x_match_rec.attribute15
FROM dual;
g_lines_tab.DELETE;
SELECT *
FROM RLM_INTERFACE_LINES_ALL --bug 4907839
WHERE schedule_item_num = v_Group_rec.schedule_item_num
AND header_id = g_header_rec.header_id
AND process_status = rlm_core_sv.k_PS_AVAILABLE
--ORDER BY item_detail_type desc
FOR UPDATE NOWAIT;
SELECT *
FROM RLM_INTERFACE_LINES_ALL --Bug 4907839
WHERE header_id = g_header_rec.header_id
AND cust_ship_from_org_ext =
v_Group_rec.cust_ship_from_org_ext
AND cust_ship_to_ext =
v_Group_rec.cust_ship_to_ext
AND customer_item_ext = v_Group_rec.customer_item_ext
AND process_status = rlm_core_sv.k_PS_AVAILABLE
--ORDER BY item_detail_type desc
FOR UPDATE NOWAIT;
SELECT *
FROM RLM_INTERFACE_LINES_ALL --bug 4907839
WHERE header_id = g_header_rec.header_id
AND nvl(cust_ship_from_org_ext,k_VNULL) =
nvl(v_Group_rec.cust_ship_from_org_ext,k_VNULL)
AND nvl(cust_ship_to_ext,k_VNULL) =
nvl(v_Group_rec.cust_ship_to_ext,k_VNULL)
AND customer_item_ext = v_Group_rec.customer_item_ext
AND process_status = rlm_core_sv.k_PS_AVAILABLE
--ORDER BY item_detail_type desc
FOR UPDATE NOWAIT;
SELECT *
FROM RLM_INTERFACE_LINES
WHERE header_id = g_header_rec.header_id
AND customer_item_id = v_Group_rec.customer_item_id
AND ship_from_org_id = v_Group_rec.ship_from_org_id
AND ship_to_address_id = v_Group_rec.ship_to_address_id
AND process_status = rlm_core_sv.k_PS_AVAILABLE
--ORDER BY item_detail_type desc
FOR UPDATE NOWAIT;
rlm_core_sv.dlog(C_DEBUG,'inserted line_id',v_lines_rec.line_id);
SELECT *
INTO g_header_rec
FROM rlm_interface_headers
WHERE header_id = x_header_id
FOR UPDATE NOWAIT;
rlm_core_sv.dlog(C_DEBUG,'No of headers selected',SQL%ROWCOUNT);
SELECT MIN(start_date_time)
INTO v_start_date
FROM rlm_interface_lines
WHERE header_id = g_header_rec.header_id;
SELECT MAX(end_date_time)
INTO v_end_date_f
FROM rlm_interface_lines
WHERE header_id = g_header_rec.header_id
AND item_detail_type IN (0,1,2,6) --Bug 5478817
AND item_detail_subtype = rlm_ship_delivery_pattern_sv.g_FLEXIBLE;
SELECT MAX(start_date_time)
INTO v_End_Date_d
FROM rlm_interface_lines
WHERE header_id = g_header_rec.header_id
AND item_detail_type IN (0,1,2,6) --Bug 5478817
AND item_detail_subtype = rlm_ship_delivery_pattern_sv.g_DAY;
SELECT MAX(start_date_time)
INTO v_End_Date_w
FROM rlm_interface_lines
WHERE header_id = g_header_rec.header_id
AND item_detail_type IN (0,1,2,6) --Bug 5478817
AND item_detail_subtype = rlm_ship_delivery_pattern_sv.g_WEEK;
SELECT MAX(start_date_time)
INTO v_End_Date_m
FROM rlm_interface_lines
WHERE header_id = g_header_rec.header_id
AND item_detail_type IN (0,1,2,6) --Bug 5478817
AND item_detail_subtype = rlm_ship_delivery_pattern_sv.g_MONTH;
SELECT NEXT_DAY(v_End_Date_w,
to_char(to_date(v_SundayDate, 'DD/MM/RRRR'), 'DY'))
INTO v_End_Date_w
FROM DUAL;
SELECT LAST_DAY(v_End_Date_m)
INTO v_End_Date_m
FROM DUAL;
SELECT count(*)
INTO v_Count
FROM rlm_interface_lines
where header_id = x_header_rec.header_id;
SELECT sched_generation_date,
edi_control_num_2,
edi_control_num_3,
schedule_reference_num,
process_status,
schedule_purpose,
DECODE(schedule_purpose, 'ADD', 1, 'CONFIRMATION', 2, 'ORIGINAL', 3, 'REPLACE', 4,
'REPLACE_ALL', 5, 'CANCELLATION', 6, 'CHANGE', 7, 'DELETE', 8),
creation_date
FROM rlm_schedule_headers
WHERE ece_tp_translator_code = x_header_rec.ece_tp_translator_code
AND ece_tp_location_code_ext = x_header_rec.ece_tp_location_code_ext
AND schedule_type = x_header_rec.schedule_type
AND interface_header_id <> x_header_rec.header_id
AND schedule_source <> 'MANUAL'
AND process_status IN (5,7)
ORDER BY sched_generation_date DESC,
edi_control_num_2 DESC,
edi_control_num_3 DESC,
schedule_reference_num DESC,
DECODE(schedule_purpose, 'ADD', 1, 'CONFIRMATION', 2, 'ORIGINAL', 3,
'REPLACE', 4, 'REPLACE_ALL', 5, 'CANCELLATION', 6, 'CHANGE', 7, 'DELETE', 8) DESC,
creation_date DESC;
SELECT DECODE(schedule_purpose, 'ADD', 1,
'CONFIRMATION', 2, 'ORIGINAL', 3,'REPLACE', 4,
'REPLACE_ALL', 5, 'CANCELLATION', 6,'CHANGE', 7, 'DELETE', 8)
FROM rlm_interface_headers
WHERE header_id = x_header_rec.header_id;
IS SELECT 1
FROM fnd_lookups
WHERE lookup_type = p_lookup_type
AND lookup_code = p_lookup_code
AND p_date between nvl(start_date_active,to_date('01/01/1900','dd/mm/yyyy'))
AND nvl(end_date_active, to_date('31/12/4712','dd/mm/yyyy'));
SELECT sched_generation_date,
edi_control_num_2,
edi_control_num_3,
schedule_reference_num,
process_status,
schedule_purpose,
DECODE(schedule_purpose, 'ADD', 1, 'CONFIRMATION', 2, 'ORIGINAL', 3, 'REPLACE', 4,
'REPLACE_ALL', 5, 'CANCELLATION', 6, 'CHANGE', 7, 'DELETE', 8),
creation_date
FROM rlm_interface_headers
WHERE ece_tp_translator_code = x_header_rec.ece_tp_translator_code
AND ece_tp_location_code_ext = x_header_rec.ece_tp_location_code_ext
AND schedule_type = x_header_rec.schedule_type
AND header_id <> x_header_rec.header_id
AND schedule_source <> 'MANUAL'
ORDER BY sched_generation_date,
edi_control_num_2,
edi_control_num_3,
schedule_reference_num ,
DECODE(schedule_purpose, 'ADD', 1, 'CONFIRMATION', 2, 'ORIGINAL', 3,
'REPLACE', 4, 'REPLACE_ALL', 5, 'CANCELLATION', 6, 'CHANGE', 7, 'DELETE', 8),
creation_date;
IF x_header_rec.schedule_purpose IN (k_CANCEL, k_CHANGE, k_DELETE)THEN
--
SELECT count(*)
INTO v_sch_pur
FROM rlm_interface_headers
WHERE ece_tp_translator_code = x_header_rec.ece_tp_translator_code
AND ece_tp_location_code_ext=x_header_rec.ece_tp_location_code_ext
AND schedule_type=x_header_rec.schedule_type
AND sched_generation_date = x_header_rec.sched_generation_date
AND schedule_reference_num = x_header_rec.schedule_reference_num
AND schedule_purpose IN (
DECODE (x_header_rec.schedule_purpose, k_CANCEL, k_DELETE,
k_DELETE, k_CANCEL, k_CHANGE, k_DELETE),
DECODE (x_header_rec.schedule_purpose, k_CANCEL,
k_CHANGE, k_DELETE, k_CHANGE, k_CHANGE, k_CANCEL),
k_ORIGINAL, k_REPLACE, k_REPLACE_ALL, k_ADD);
CANCEL, CHANGE, DELETE');
SELECT DECODE(schedule_purpose, 'ADD',1,
'CONFIRMATION', 2, 'ORIGINAL', 3,'REPLACE', 4,
'REPLACE_ALL', 5, 'CANCELLATION', 6,'CHANGE', 7, 'DELETE', 8)
FROM rlm_interface_headers
WHERE header_id = x_header_rec.header_id;
SELECT customer_order_enabled_flag
FROM mtl_system_items
WHERE inventory_item_id = p_InvItemId AND
organization_id = p_ShipFromOrgId;
SELECT inventory_item_id
INTO v_InventoryItemidFromSup
FROM mtl_item_flexfields
WHERE item_number = x_lines_rec.supplier_item_ext
AND organization_id = x_lines_rec.ship_from_org_id
AND customer_order_enabled_flag = 'Y';
SELECT inventory_item_id
INTO v_InventoryItemIdFromSeg
FROM mtl_system_items
WHERE segment1 = x_lines_rec.inventory_item_segment1
AND segment2 = x_lines_rec.inventory_item_segment2
AND segment3 = x_lines_rec.inventory_item_segment3
AND segment4 = x_lines_rec.inventory_item_segment4
AND segment5 = x_lines_rec.inventory_item_segment5
AND segment6 = x_lines_rec.inventory_item_segment6
AND segment7 = x_lines_rec.inventory_item_segment7
AND segment8 = x_lines_rec.inventory_item_segment8
AND segment9 = x_lines_rec.inventory_item_segment9
AND segment10 = x_lines_rec.inventory_item_segment10
AND segment11 = x_lines_rec.inventory_item_segment11
AND segment12 = x_lines_rec.inventory_item_segment12
AND segment13 = x_lines_rec.inventory_item_segment13
AND segment14 = x_lines_rec.inventory_item_segment14
AND segment15 = x_lines_rec.inventory_item_segment15
AND segment16 = x_lines_rec.inventory_item_segment16
AND segment17 = x_lines_rec.inventory_item_segment17
AND segment18 = x_lines_rec.inventory_item_segment18
AND segment19 = x_lines_rec.inventory_item_segment19
AND segment20 = x_lines_rec.inventory_item_segment20
AND customer_order_enabled_flag = 'Y';
SELECT customer_item_id
INTO x_lines_rec.customer_item_id
FROM mtl_customer_items
WHERE customer_item_number = x_lines_rec.customer_item_ext
AND customer_id = x_header_rec.customer_id
AND address_id = x_lines_rec.ship_to_address_id
AND item_definition_level = '3'
AND inactive_flag = 'N';
SELECT mci.customer_item_id
INTO x_lines_rec.customer_item_id
FROM mtl_customer_items mci
WHERE mci.customer_item_number = x_lines_rec.customer_item_ext
AND mci.customer_id = x_header_rec.customer_id
AND x_lines_rec.ship_to_address_id IN
(select cust_acct_site_id from hz_cust_acct_sites
where customer_category_code = mci.customer_category_code
and cust_account_id = mci.customer_id)
AND mci.item_definition_level = '2'
AND mci.inactive_flag = 'N';
SELECT customer_item_id
INTO x_lines_rec.customer_item_id
FROM mtl_customer_items
WHERE customer_item_number = x_lines_rec.customer_item_ext
AND address_id IS NULL
AND customer_id = x_header_rec.customer_id
AND item_definition_level = '1'
AND inactive_flag = 'N';
SELECT oea.price_list_id, oea.purchase_order_num
INTO x_lines_rec.price_list_id,
v_OE_Purchase_Order
FROM oe_agreements oea
WHERE oea.agreement_id = v_agreement_id
AND v_start_date between nvl(oea.start_date_active, to_date('01/01/1900','dd/mm/yyyy'))
and nvl(oea.end_date_active, to_date('31/12/4712','dd/mm/yyyy'));
SELECT oea.price_list_id, oea.purchase_order_num
INTO x_lines_rec.price_list_id,
v_OE_Purchase_Order
FROM oe_agreements oea
WHERE agreement_id = v_fut_agreement_id
AND v_start_date between nvl(oea.start_date_active, to_date('01/01/1900','dd/mm/yyyy') )
and nvl(oea.end_date_active, to_date('31/12/4712','dd/mm/yyyy'));
SELECT oea.agreement_id, oea.name,
oea.price_list_id,oea.start_date_active,
oea.end_date_active
INTO x_lines_rec.agreement_id, v_agreement_name,
x_lines_rec.price_list_id, v_start_date, v_end_date
FROM oe_agreements oea
WHERE purchase_order_num = x_lines_rec.cust_po_number
AND x_lines_rec.start_date_time
BETWEEN oea.start_date_active AND
nvl(oea.end_date_active,
to_date('31/12/4712','dd/mm/yyyy'))
AND revision = (Select MAX(oem.revision)
FROM oe_agreements oem
WHERE oem.purchase_order_num =
x_lines_rec.cust_po_number);
SELECT CUST_ACCT.PRICE_LIST_ID
INTO x_lines_rec.PRICE_LIST_ID
FROM HZ_CUST_ACCOUNTS CUST_ACCT
WHERE CUST_ACCT.CUST_ACCOUNT_ID = x_header_rec.customer_id;
SELECT oep.name
INTO v_Price_list_name
FROM qp_list_headers oep
WHERE oep.LIST_HEADER_ID = x_lines_rec.price_list_id
AND x_lines_rec.start_date_time BETWEEN
nvl(oep.start_date_active,
to_date('01/01/1900','dd/mm/yyyy')) AND
nvl(oep.end_date_active,to_date('31/12/4712','dd/mm/yyyy'));
SELECT DISTINCT ACCT_SITE.CUST_ACCOUNT_ID
INTO x_header_rec.customer_id
FROM HZ_CUST_ACCT_SITES ACCT_SITE ,
ece_tp_headers eth
WHERE ACCT_SITE.tp_header_id = eth.tp_header_id
AND ACCT_SITE.ece_tp_location_code = v_ShipToLoc
AND eth.TP_REFERENCE_EXT1 = v_Customer;
SELECT ship_from_org_id
INTO x_lines_rec.ship_from_org_id
FROM rlm_cust_shipto_terms t
WHERE customer_id = x_header_rec.customer_id
AND address_id = nvl(x_lines_rec.ship_to_address_id,address_id)
AND cust_assign_supplier_cd =
x_lines_rec.cust_ship_from_org_ext;
SELECT ship_from_org_id
INTO x_lines_rec.ship_from_org_id
FROM rlm_cust_shipto_terms t
WHERE customer_id = x_header_rec.customer_id AND
address_id IS NULL AND
cust_assign_supplier_cd =
x_lines_rec.cust_ship_from_org_ext;
SELECT ACCT_SITE.CUST_ACCT_SITE_ID,
ACCT_SITE.STATUS, SITE_USES.STATUS,
SITE_USES.site_use_id
FROM HZ_CUST_ACCT_SITES ACCT_SITE,
HZ_CUST_SITE_USES_ALL SITE_USES
WHERE acct_site.ece_tp_location_code =
x_lines_rec.cust_intrmd_ship_to_ext
AND acct_site.cust_acct_site_id = site_uses.cust_acct_site_id
AND site_uses.site_use_code = 'SHIP_TO'
AND acct_site.org_id = site_uses.org_id
AND cust_account_id IN
(SELECT to_number(x_header_rec.customer_id) from dual
UNION
SELECT cust_account_id
FROM hz_cust_acct_relate_all
WHERE related_cust_account_id = x_header_rec.customer_id
AND ship_to_flag = 'Y'
AND status = 'A'
AND org_id = x_header_rec.org_id
AND oe_sys_parameters.value('CUSTOMER_RELATIONSHIPS_FLAG') IN ('Y', 'A'))
ORDER BY site_uses.status; --To query first Active and then Inactive records
SELECT hcas.status, cust_account_id, hcsu.status,
hcsu.site_use_id
FROM hz_cust_acct_sites_all hcas, hz_cust_site_uses_all hcsu
WHERE hcas.cust_acct_site_id = x_lines_rec.intrmd_ship_to_id
AND hcas.cust_acct_site_id = hcsu.cust_acct_site_id
AND hcsu.site_use_code = 'SHIP_TO'
AND hcas.org_id = hcsu.org_id
ORDER BY hcsu.status; --To query first Active and then Inactive records
SELECT site_use_id ,
status
INTO x_lines_rec.intrmd_st_site_use_id,
v_status
FROM HZ_CUST_SITE_USES
WHERE CUST_ACCT_SITE_ID = x_lines_rec.INTRMD_SHIP_TO_ID
AND site_use_code = 'SHIP_TO';
SELECT ACCT_SITE.CUST_ACCT_SITE_ID,
ACCT_SITE.STATUS, SITE_USE.STATUS,
SITE_USE.site_use_id
FROM HZ_CUST_ACCT_SITES ACCT_SITE, HZ_CUST_SITE_USES_ALL SITE_USE
WHERE acct_site.ece_tp_location_code = x_lines_rec.cust_bill_to_ext
AND ACCT_SITE.CUST_ACCOUNT_ID = x_header_rec.customer_id
AND site_use_code = 'BILL_TO'
AND ACCT_SITE.cust_acct_site_id = SITE_USE.cust_acct_site_id
AND ACCT_SITE.org_id = SITE_USE.org_id
ORDER BY SITE_USE.status; --To query first Active and then Inactive records
SELECT ACCT_SITE.CUST_ACCT_SITE_ID,
ACCT_SITE.status,
ACCT_SITE.CUST_ACCOUNT_ID, SITE_USE.STATUS,
SITE_USE.site_use_id
FROM HZ_CUST_ACCT_SITES ACCT_SITE, HZ_CUST_SITE_USES_ALL SITE_USE
WHERE ACCT_SITE.ece_tp_location_code = x_lines_rec.cust_bill_to_ext
AND ACCT_SITE.cust_acct_site_id = SITE_USE.cust_acct_site_id
AND SITE_USE.site_use_code = 'BILL_TO'
AND ACCT_SITE.org_id = SITE_USE.org_id
AND ACCT_SITE.CUST_ACCOUNT_ID in
(SELECT DISTINCT cust_account_id
FROM HZ_CUST_ACCT_RELATE_ALL
WHERE related_cust_account_id = x_header_rec.customer_id
AND status='A'
AND bill_to_flag = 'Y'
AND oe_sys_parameters.value('CUSTOMER_RELATIONSHIPS_FLAG', x_header_rec.org_id) IN ('Y', 'A')
AND org_id = x_header_rec.org_id)
ORDER BY SITE_USE.status; --To query first Active and then Inactive records
SELECT site_use_id, status
FROM HZ_CUST_SITE_USES
WHERE CUST_ACCT_SITE_ID = x_lines_rec.ship_to_address_id
AND site_use_code = 'SHIP_TO'
ORDER BY status; --To query first Active and then Inactive records
SELECT ACCT_SITE.STATUS, ACCT_SITE.CUST_ACCOUNT_ID,
SITE_USE.STATUS, SITE_USE.site_use_id
FROM HZ_CUST_ACCT_SITES ACCT_SITE, HZ_CUST_SITE_USES_ALL SITE_USE
WHERE ACCT_SITE.CUST_ACCT_SITE_ID = x_lines_rec.bill_to_address_id
AND ACCT_SITE.cust_acct_site_id = SITE_USE.cust_acct_site_id
AND SITE_USE.site_use_code = 'BILL_TO'
AND ACCT_SITE.org_id = SITE_USE.org_id
ORDER BY SITE_USE.status; --To query first Active and then Inactive records
SELECT CUST_ACCT_SITE_ID, status
INTO x_lines_rec.bill_to_address_id, v_status
FROM HZ_CUST_SITE_USES_ALL
WHERE SITE_USE_ID = x_lines_rec.bill_to_site_use_id;
SELECT hz.cust_acct_site_id, hz.status, cust_account_id,
hcsu.status, site_use_id
FROM hz_cust_acct_sites hz, hz_cust_site_uses_all hcsu
WHERE hz.ece_tp_location_code = x_lines_rec.cust_ship_to_ext
AND hz.cust_acct_site_id = hcsu.cust_acct_site_id
AND site_use_code = 'SHIP_TO'
AND hz.org_id = hcsu.org_id
AND hz.cust_account_id IN
(SELECT to_number(x_header_rec.customer_id) from dual
UNION
SELECT cust_account_id
FROM hz_cust_acct_relate_all
WHERE related_cust_account_id = x_header_rec.customer_id
AND ship_to_flag = 'Y'
AND status = 'A'
AND org_id = x_header_rec.org_id
AND oe_sys_parameters.value('CUSTOMER_RELATIONSHIPS_FLAG')
IN ('Y', 'A'))
ORDER BY hcsu.status; --To query first Active and then Inactive records
SELECT hcas.cust_acct_site_id, hcas.status, hcsu.status,
hcsu.site_use_id
FROM hz_cust_acct_sites hcas, hz_cust_site_uses_all hcsu
WHERE ece_tp_location_code = x_header_rec.ece_tp_location_code_ext
AND hcas.cust_account_id = x_header_rec.customer_id
AND hcas.cust_acct_site_id = hcsu.cust_acct_site_id
AND site_use_code = 'SHIP_TO'
AND hcas.org_id = hcsu.org_id
ORDER BY hcsu.status; --To query first Active and then Inactive records
SELECT hcas.status, cust_account_id, hcsu.status, hcsu.site_use_id
FROM hz_cust_acct_sites_all hcas, hz_cust_site_uses_all hcsu
WHERE hcas.cust_acct_site_id = x_lines_rec.ship_to_address_id
AND hcas.cust_acct_site_id = hcsu.cust_acct_site_id
AND hcsu.site_use_code = 'SHIP_TO'
AND hcas.org_id = hcsu.org_id
ORDER BY hcsu.status; --To query first Active and then Inactive records
SELECT rasu.cust_acct_site_id, rad.status, rasu.status,
rasu.site_use_id
INTO x_header_rec.ece_primary_address_id, v_addStatus, v_siteUseStatus,
x_lines_rec.ship_to_site_use_id
FROM hz_cust_acct_sites rad,
hz_cust_site_uses_all rasu
WHERE rad.cust_acct_site_id = rasu.cust_acct_site_id
AND rasu.site_use_code = 'SHIP_TO'
AND rasu.primary_flag = 'Y'
AND rad.cust_account_id = x_header_rec.customer_id
AND rad.status = 'A'
AND rad.org_id = rasu.org_id;
SELECT site_use_id , status
INTO x_lines_rec.ship_to_site_use_id, v_status
FROM HZ_CUST_SITE_USES
WHERE CUST_ACCT_SITE_ID = x_lines_rec.ship_to_address_id
AND site_use_code = 'SHIP_TO';
SELECT nvl(customer_item_id, x_lines_rec.customer_item_id),
nvl(inventory_item_id, x_lines_rec.inventory_item_id),
nvl(ship_from_org_id, x_lines_rec.ship_from_org_id),
nvl(intrmd_ship_to_id, x_lines_rec.intrmd_ship_to_id),
nvl(ship_to_address_id, x_lines_rec.ship_to_address_id),
nvl(bill_to_address_id, x_lines_rec.bill_to_address_id),
cust_po_number ,
start_date_time,
industry_attribute1 -- cust_record_year
FROM rlm_interface_lines
WHERE header_id = x_header_rec.header_id
AND schedule_item_num = x_group_rec.schedule_item_num
AND item_detail_type = '4'
AND item_detail_subtype = 'CUM';
PROCEDURE NAME: UpdateInterfaceLines
===========================================================================*/
PROCEDURE UpdateInterfaceLines(x_header_rec IN RLM_INTERFACE_HEADERS%ROWTYPE)
IS
--
v_Progress VARCHAR(3) := '010';
v_last_update_date DATE := sysdate;
v_last_updated_by NUMBER := fnd_global.user_id;
v_last_update_login NUMBER := fnd_global.login_id;
v_program_update_date DATE := sysdate;
rlm_core_sv.dpush(C_SDEBUG,'UpdateInterfaceLines');
/* oracle 9i compatible bulk update
FORALL i in 1..g_lines_tab.COUNT
UPDATE rlm_interface_lines
SET ROW = g_lines_tab(i)
WHERE header_id = header_id_tab(i)
AND line_id = line_id_tab(i);
UPDATE rlm_interface_lines
SET
AGREEMENT_ID = AGREEMENT_ID_TAB(i),
ATO_DATA_TYPE = ATO_DATA_TYPE_TAB(i),
BILL_TO_ADDRESS_1_EXT = BILL_TO_ADDRESS_1_EXT_TAB(i),
BILL_TO_ADDRESS_2_EXT = BILL_TO_ADDRESS_2_EXT_TAB(i),
BILL_TO_ADDRESS_3_EXT = BILL_TO_ADDRESS_3_EXT_TAB(i),
BILL_TO_ADDRESS_4_EXT = BILL_TO_ADDRESS_4_EXT_TAB(i),
BILL_TO_ADDRESS_ID = BILL_TO_ADDRESS_ID_TAB(i),
INVOICE_TO_ORG_ID = INVOICE_TO_ORG_ID_TAB(i),
BILL_TO_CITY_EXT = BILL_TO_CITY_EXT_TAB(i),
BILL_TO_COUNTRY_EXT = BILL_TO_COUNTRY_EXT_TAB(i),
BILL_TO_COUNTY_EXT = BILL_TO_COUNTY_EXT_TAB(i),
BILL_TO_NAME_EXT = BILL_TO_NAME_EXT_TAB(i),
BILL_TO_POSTAL_CD_EXT = BILL_TO_POSTAL_CD_EXT_TAB(i),
BILL_TO_PROVINCE_EXT = BILL_TO_PROVINCE_EXT_TAB(i),
BILL_TO_SITE_USE_ID = BILL_TO_SITE_USE_ID_TAB(i),
BILL_TO_STATE_EXT = BILL_TO_STATE_EXT_TAB(i),
CARRIER_ID_CODE_EXT = CARRIER_ID_CODE_EXT_TAB(i),
CARRIER_QUALIFIER_EXT = CARRIER_QUALIFIER_EXT_TAB(i),
COMMODITY_EXT = COMMODITY_EXT_TAB(i),
COUNTRY_OF_ORIGIN_EXT = COUNTRY_OF_ORIGIN_EXT_TAB(i),
CUST_ASSEMBLY_EXT = CUST_ASSEMBLY_EXT_TAB(i),
CUST_ASSIGNED_ID_EXT = CUST_ASSIGNED_ID_EXT_TAB(i),
CUST_BILL_TO_EXT = CUST_BILL_TO_EXT_TAB(i),
CUST_CONTRACT_NUM_EXT = CUST_CONTRACT_NUM_EXT_TAB(i),
CUSTOMER_DOCK_CODE = CUSTOMER_DOCK_CODE_TAB(i),
CUST_INTRMD_SHIP_TO_EXT = CUST_INTRMD_SHIP_TO_EXT_TAB(i),
CUST_ITEM_PRICE_EXT = CUST_ITEM_PRICE_EXT_TAB(i),
CUST_ITEM_PRICE_UOM_EXT = CUST_ITEM_PRICE_UOM_EXT_TAB(i),
CUSTOMER_ITEM_REVISION = CUSTOMER_ITEM_REVISION_TAB(i),
CUSTOMER_JOB = CUSTOMER_JOB_TAB(i),
CUST_MANUFACTURER_EXT = CUST_MANUFACTURER_EXT_TAB(i),
CUST_MODEL_NUMBER_EXT = CUST_MODEL_NUMBER_EXT_TAB(i),
CUST_MODEL_SERIAL_NUMBER = CUST_MODEL_SERIAL_NUMBER_TAB(i),
CUST_ORDER_NUM_EXT = CUST_ORDER_NUM_EXT_TAB(i),
CUST_PROCESS_NUM_EXT = CUST_PROCESS_NUM_EXT_TAB(i),
CUST_SET_NUM_EXT = CUST_SET_NUM_EXT_TAB(i),
CUST_SHIP_FROM_ORG_EXT = CUST_SHIP_FROM_ORG_EXT_TAB(i),
CUST_SHIP_TO_EXT = CUST_SHIP_TO_EXT_TAB(i),
CUST_UOM_EXT = CUST_UOM_EXT_TAB(i),
CUSTOMER_ITEM_EXT = CUSTOMER_ITEM_EXT_TAB(i),
CUSTOMER_ITEM_ID = CUSTOMER_ITEM_ID_TAB(i),
REQUEST_DATE = REQUEST_DATE_TAB(i),
SCHEDULE_DATE = SCHEDULE_DATE_TAB(i),
DATE_TYPE_CODE = DATE_TYPE_CODE_TAB(i),
DATE_TYPE_CODE_EXT = DATE_TYPE_CODE_EXT_TAB(i),
DELIVERY_LEAD_TIME = DELIVERY_LEAD_TIME_TAB(i),
END_DATE_TIME = END_DATE_TIME_TAB(i),
EQUIPMENT_CODE_EXT = EQUIPMENT_CODE_EXT_TAB(i),
EQUIPMENT_NUMBER_EXT = EQUIPMENT_NUMBER_EXT_TAB(i),
HANDLING_CODE_EXT = HANDLING_CODE_EXT_TAB(i),
HAZARD_CODE_EXT = HAZARD_CODE_EXT_TAB(i),
HAZARD_CODE_QUAL_EXT = HAZARD_CODE_QUAL_EXT_TAB(i),
HAZARD_DESCRIPTION_EXT = HAZARD_DESCRIPTION_EXT_TAB(i),
IMPORT_LICENSE_DATE_EXT = IMPORT_LICENSE_DATE_EXT_TAB(i),
IMPORT_LICENSE_EXT = IMPORT_LICENSE_EXT_TAB(i),
INDUSTRY_ATTRIBUTE1 = INDUSTRY_ATTRIBUTE1_TAB(i),
INDUSTRY_ATTRIBUTE10 = INDUSTRY_ATTRIBUTE10_TAB(i),
INDUSTRY_ATTRIBUTE11 = INDUSTRY_ATTRIBUTE11_TAB(i),
INDUSTRY_ATTRIBUTE12 = INDUSTRY_ATTRIBUTE12_TAB(i),
INDUSTRY_ATTRIBUTE13 = INDUSTRY_ATTRIBUTE13_TAB(i),
INDUSTRY_ATTRIBUTE14 = INDUSTRY_ATTRIBUTE14_TAB(i),
INDUSTRY_ATTRIBUTE15 = INDUSTRY_ATTRIBUTE15_TAB(i),
INDUSTRY_ATTRIBUTE2 = INDUSTRY_ATTRIBUTE2_TAB(i),
INDUSTRY_ATTRIBUTE3 = INDUSTRY_ATTRIBUTE3_TAB(i),
INDUSTRY_ATTRIBUTE4 = INDUSTRY_ATTRIBUTE4_TAB(i),
INDUSTRY_ATTRIBUTE5 = INDUSTRY_ATTRIBUTE5_TAB(i),
INDUSTRY_ATTRIBUTE6 = INDUSTRY_ATTRIBUTE6_TAB(i),
INDUSTRY_ATTRIBUTE7 = INDUSTRY_ATTRIBUTE7_TAB(i),
INDUSTRY_ATTRIBUTE8 = INDUSTRY_ATTRIBUTE8_TAB(i),
INDUSTRY_ATTRIBUTE9 = INDUSTRY_ATTRIBUTE9_TAB(i),
INDUSTRY_CONTEXT = INDUSTRY_CONTEXT_TAB(i),
INTRMD_SHIP_TO_ID = INTRMD_SHIP_TO_ID_TAB(i),
SHIP_TO_ORG_ID = SHIP_TO_ORG_ID_TAB(i),
INTRMD_ST_ADDRESS_1_EXT = INTRMD_ST_ADDRESS_1_EXT_TAB(i),
INTRMD_ST_ADDRESS_2_EXT = INTRMD_ST_ADDRESS_2_EXT_TAB(i),
INTRMD_ST_ADDRESS_3_EXT = INTRMD_ST_ADDRESS_3_EXT_TAB(i),
INTRMD_ST_ADDRESS_4_EXT = INTRMD_ST_ADDRESS_4_EXT_TAB(i),
INTRMD_ST_CITY_EXT = INTRMD_ST_CITY_EXT_TAB(i),
INTRMD_ST_COUNTRY_EXT = INTRMD_ST_COUNTRY_EXT_TAB(i),
INTRMD_ST_COUNTY_EXT = INTRMD_ST_COUNTY_EXT_TAB(i),
INTRMD_ST_NAME_EXT = INTRMD_ST_NAME_EXT_TAB(i),
INTRMD_ST_POSTAL_CD_EXT = INTRMD_ST_POSTAL_CD_EXT_TAB(i),
INTRMD_ST_PROVINCE_EXT = INTRMD_ST_PROVINCE_EXT_TAB(i),
INTRMD_ST_STATE_EXT = INTRMD_ST_STATE_EXT_TAB(i),
INTRMD_ST_SITE_USE_ID = INTRMD_ST_SITE_USE_ID_TAB(i),
INVENTORY_ITEM_ID = INVENTORY_ITEM_ID_TAB(i),
INVENTORY_ITEM_SEGMENT1 = INVENTORY_ITEM_SEGMENT1_TAB(i),
INVENTORY_ITEM_SEGMENT10 = INVENTORY_ITEM_SEGMENT10_TAB(i),
INVENTORY_ITEM_SEGMENT11 = INVENTORY_ITEM_SEGMENT11_TAB(i),
INVENTORY_ITEM_SEGMENT12 = INVENTORY_ITEM_SEGMENT12_TAB(i),
INVENTORY_ITEM_SEGMENT13 = INVENTORY_ITEM_SEGMENT13_TAB(i),
INVENTORY_ITEM_SEGMENT14 = INVENTORY_ITEM_SEGMENT14_TAB(i),
INVENTORY_ITEM_SEGMENT15 = INVENTORY_ITEM_SEGMENT15_TAB(i),
INVENTORY_ITEM_SEGMENT16 = INVENTORY_ITEM_SEGMENT16_TAB(i),
INVENTORY_ITEM_SEGMENT17 = INVENTORY_ITEM_SEGMENT17_TAB(i),
INVENTORY_ITEM_SEGMENT18 = INVENTORY_ITEM_SEGMENT18_TAB(i),
INVENTORY_ITEM_SEGMENT19 = INVENTORY_ITEM_SEGMENT19_TAB(i),
INVENTORY_ITEM_SEGMENT2 = INVENTORY_ITEM_SEGMENT2_TAB(i),
INVENTORY_ITEM_SEGMENT20 = INVENTORY_ITEM_SEGMENT20_TAB(i),
INVENTORY_ITEM_SEGMENT3 = INVENTORY_ITEM_SEGMENT3_TAB(i),
INVENTORY_ITEM_SEGMENT4 = INVENTORY_ITEM_SEGMENT4_TAB(i),
INVENTORY_ITEM_SEGMENT5 = INVENTORY_ITEM_SEGMENT5_TAB(i),
INVENTORY_ITEM_SEGMENT6 = INVENTORY_ITEM_SEGMENT6_TAB(i),
INVENTORY_ITEM_SEGMENT7 = INVENTORY_ITEM_SEGMENT7_TAB(i),
INVENTORY_ITEM_SEGMENT8 = INVENTORY_ITEM_SEGMENT8_TAB(i),
INVENTORY_ITEM_SEGMENT9 = INVENTORY_ITEM_SEGMENT9_TAB(i),
ITEM_CONTACT_CODE_1 = ITEM_CONTACT_CODE_1_TAB(i),
ITEM_CONTACT_CODE_2 = ITEM_CONTACT_CODE_2_TAB(i),
ITEM_CONTACT_VALUE_1 = ITEM_CONTACT_VALUE_1_TAB(i),
ITEM_CONTACT_VALUE_2 = ITEM_CONTACT_VALUE_2_TAB(i),
ITEM_DESCRIPTION_EXT = ITEM_DESCRIPTION_EXT_TAB(i),
ITEM_DETAIL_QUANTITY = ITEM_DETAIL_QUANTITY_TAB(i),
ITEM_DETAIL_REF_CODE_1 = ITEM_DETAIL_REF_CODE_1_TAB(i),
ITEM_DETAIL_REF_CODE_2 = ITEM_DETAIL_REF_CODE_2_TAB(i),
ITEM_DETAIL_REF_CODE_3 = ITEM_DETAIL_REF_CODE_3_TAB(i),
ITEM_DETAIL_REF_VALUE_1 = ITEM_DETAIL_REF_VALUE_1_TAB(i),
ITEM_DETAIL_REF_VALUE_2 = ITEM_DETAIL_REF_VALUE_2_TAB(i),
ITEM_DETAIL_REF_VALUE_3 = ITEM_DETAIL_REF_VALUE_3_TAB(i),
ITEM_DETAIL_SUBTYPE = ITEM_DETAIL_SUBTYPE_TAB(i),
ITEM_DETAIL_SUBTYPE_EXT = ITEM_DETAIL_SUBTYPE_EXT_TAB(i),
ITEM_DETAIL_TYPE = ITEM_DETAIL_TYPE_TAB(i),
ITEM_DETAIL_TYPE_EXT = ITEM_DETAIL_TYPE_EXT_TAB(i),
ITEM_ENG_CNG_LVL_EXT = ITEM_ENG_CNG_LVL_EXT_TAB(i),
ITEM_MEASUREMENTS_EXT = ITEM_MEASUREMENTS_EXT_TAB(i),
ITEM_NOTE_TEXT = ITEM_NOTE_TEXT_TAB(i),
ITEM_REF_CODE_1 = ITEM_REF_CODE_1_TAB(i),
ITEM_REF_CODE_2 = ITEM_REF_CODE_2_TAB(i),
ITEM_REF_CODE_3 = ITEM_REF_CODE_3_TAB(i),
ITEM_REF_VALUE_1 = ITEM_REF_VALUE_1_TAB(i),
ITEM_REF_VALUE_2 = ITEM_REF_VALUE_2_TAB(i),
ITEM_REF_VALUE_3 = ITEM_REF_VALUE_3_TAB(i),
ITEM_RELEASE_STATUS_EXT = ITEM_RELEASE_STATUS_EXT_TAB(i),
LADING_QUANTITY_EXT = LADING_QUANTITY_EXT_TAB(i),
LETTER_CREDIT_EXPDT_EXT = LETTER_CREDIT_EXPDT_EXT_TAB(i),
LETTER_CREDIT_EXT = LETTER_CREDIT_EXT_TAB(i),
LINE_REFERENCE = LINE_REFERENCE_TAB(i),
LINK_TO_LINE_REF = LINK_TO_LINE_REF_TAB(i),
ORDER_HEADER_ID = ORDER_HEADER_ID_TAB(i),
OTHER_NAME_CODE_1 = OTHER_NAME_CODE_1_TAB(i),
OTHER_NAME_CODE_2 = OTHER_NAME_CODE_2_TAB(i),
OTHER_NAME_VALUE_1 = OTHER_NAME_VALUE_1_TAB(i),
OTHER_NAME_VALUE_2 = OTHER_NAME_VALUE_2_TAB(i),
PACK_SIZE_EXT = PACK_SIZE_EXT_TAB(i),
PACK_UNITS_PER_PACK_EXT = PACK_UNITS_PER_PACK_EXT_TAB(i),
PACK_UOM_CODE_EXT = PACK_UOM_CODE_EXT_TAB(i),
PACKAGING_CODE_EXT = PACKAGING_CODE_EXT_TAB(i),
PARENT_LINK_LINE_REF = PARENT_LINK_LINE_REF_TAB(i),
CUST_PRODUCTION_SEQ_NUM = CUST_PRODUCTION_SEQ_NUM_TAB(i),
PRICE_LIST_ID = PRICE_LIST_ID_TAB(i),
PRIMARY_QUANTITY = PRIMARY_QUANTITY_TAB(i),
PRIMARY_UOM_CODE = PRIMARY_UOM_CODE_TAB(i),
PRIME_CONTRCTR_PART_EXT = PRIME_CONTRCTR_PART_EXT_TAB(i),
PROCESS_STATUS = PROCESS_STATUS_TAB(i),
CUST_PO_RELEASE_NUM = CUST_PO_RELEASE_NUM_TAB(i),
CUST_PO_DATE = CUST_PO_DATE_TAB(i),
CUST_PO_LINE_NUM = CUST_PO_LINE_NUM_TAB(i),
CUST_PO_NUMBER = CUST_PO_NUMBER_TAB(i),
QTY_TYPE_CODE = QTY_TYPE_CODE_TAB(i),
QTY_TYPE_CODE_EXT = QTY_TYPE_CODE_EXT_TAB(i),
RETURN_CONTAINER_EXT = RETURN_CONTAINER_EXT_TAB(i),
SCHEDULE_LINE_ID = SCHEDULE_LINE_ID_TAB(i),
ROUTING_DESC_EXT = ROUTING_DESC_EXT_TAB(i),
ROUTING_SEQ_CODE_EXT = ROUTING_SEQ_CODE_EXT_TAB(i),
SCHEDULE_ITEM_NUM = SCHEDULE_ITEM_NUM_TAB(i),
SHIP_DEL_PATTERN_EXT = SHIP_DEL_PATTERN_EXT_TAB(i),
SHIP_DEL_TIME_CODE_EXT = SHIP_DEL_TIME_CODE_EXT_TAB(i),
SHIP_DEL_RULE_NAME = SHIP_DEL_RULE_NAME_TAB(i),
SHIP_FROM_ADDRESS_1_EXT = SHIP_FROM_ADDRESS_1_EXT_TAB(i),
SHIP_FROM_ADDRESS_2_EXT = SHIP_FROM_ADDRESS_2_EXT_TAB(i),
SHIP_FROM_ADDRESS_3_EXT = SHIP_FROM_ADDRESS_3_EXT_TAB(i),
SHIP_FROM_ADDRESS_4_EXT = SHIP_FROM_ADDRESS_4_EXT_TAB(i),
SHIP_FROM_CITY_EXT = SHIP_FROM_CITY_EXT_TAB(i),
SHIP_FROM_COUNTRY_EXT = SHIP_FROM_COUNTRY_EXT_TAB(i),
SHIP_FROM_COUNTY_EXT = SHIP_FROM_COUNTY_EXT_TAB(i),
SHIP_FROM_NAME_EXT = SHIP_FROM_NAME_EXT_TAB(i),
SHIP_FROM_ORG_ID = SHIP_FROM_ORG_ID_TAB(i),
SHIP_FROM_POSTAL_CD_EXT = SHIP_FROM_POSTAL_CD_EXT_TAB(i),
SHIP_FROM_PROVINCE_EXT = SHIP_FROM_PROVINCE_EXT_TAB(i),
SHIP_FROM_STATE_EXT = SHIP_FROM_STATE_EXT_TAB(i),
SHIP_LABEL_INFO_LINE_1 = SHIP_LABEL_INFO_LINE_1_TAB(i),
SHIP_LABEL_INFO_LINE_10 = SHIP_LABEL_INFO_LINE_10_TAB(i),
SHIP_LABEL_INFO_LINE_2 = SHIP_LABEL_INFO_LINE_2_TAB(i),
SHIP_LABEL_INFO_LINE_3 = SHIP_LABEL_INFO_LINE_3_TAB(i),
SHIP_LABEL_INFO_LINE_4 = SHIP_LABEL_INFO_LINE_4_TAB(i),
SHIP_LABEL_INFO_LINE_5 = SHIP_LABEL_INFO_LINE_5_TAB(i),
SHIP_LABEL_INFO_LINE_6 = SHIP_LABEL_INFO_LINE_6_TAB(i),
SHIP_LABEL_INFO_LINE_7 = SHIP_LABEL_INFO_LINE_7_TAB(i),
SHIP_LABEL_INFO_LINE_8 = SHIP_LABEL_INFO_LINE_8_TAB(i),
SHIP_LABEL_INFO_LINE_9 = SHIP_LABEL_INFO_LINE_9_TAB(i),
SHIP_TO_ADDRESS_1_EXT = SHIP_TO_ADDRESS_1_EXT_TAB(i),
SHIP_TO_ADDRESS_2_EXT = SHIP_TO_ADDRESS_2_EXT_TAB(i),
SHIP_TO_ADDRESS_3_EXT = SHIP_TO_ADDRESS_3_EXT_TAB(i),
SHIP_TO_ADDRESS_4_EXT = SHIP_TO_ADDRESS_4_EXT_TAB(i),
SHIP_TO_ADDRESS_ID = SHIP_TO_ADDRESS_ID_TAB(i),
DELIVER_TO_ORG_ID = DELIVER_TO_ORG_ID_TAB(i),
SHIP_TO_CITY_EXT = SHIP_TO_CITY_EXT_TAB(i),
SHIP_TO_COUNTRY_EXT = SHIP_TO_COUNTRY_EXT_TAB(i),
SHIP_TO_COUNTY_EXT = SHIP_TO_COUNTY_EXT_TAB(i),
SHIP_TO_NAME_EXT = SHIP_TO_NAME_EXT_TAB(i),
SHIP_TO_POSTAL_CD_EXT = SHIP_TO_POSTAL_CD_EXT_TAB(i),
SHIP_TO_PROVINCE_EXT = SHIP_TO_PROVINCE_EXT_TAB(i),
SHIP_TO_SITE_USE_ID = SHIP_TO_SITE_USE_ID_TAB(i),
SHIP_TO_STATE_EXT = SHIP_TO_STATE_EXT_TAB(i),
START_DATE_TIME = START_DATE_TIME_TAB(i),
SUBLINE_ASSIGNED_ID_EXT = SUBLINE_ASSIGNED_ID_EXT_TAB(i),
SUBLINE_CONFIG_CODE_EXT = SUBLINE_CONFIG_CODE_EXT_TAB(i),
SUBLINE_CUST_ITEM_EXT = SUBLINE_CUST_ITEM_EXT_TAB(i),
SUBLINE_CUST_ITEM_ID = SUBLINE_CUST_ITEM_ID_TAB(i),
SUBLINE_MODEL_NUM_EXT = SUBLINE_MODEL_NUM_EXT_TAB(i),
SUBLINE_QUANTITY = SUBLINE_QUANTITY_TAB(i),
SUBLINE_UOM_CODE = SUBLINE_UOM_CODE_TAB(i),
SUPPLIER_ITEM_EXT = SUPPLIER_ITEM_EXT_TAB(i),
TRANSIT_TIME_EXT = TRANSIT_TIME_EXT_TAB(i),
TRANSIT_TIME_QUAL_EXT = TRANSIT_TIME_QUAL_EXT_TAB(i),
TRANSPORT_LOC_QUAL_EXT = TRANSPORT_LOC_QUAL_EXT_TAB(i),
TRANSPORT_LOCATION_EXT = TRANSPORT_LOCATION_EXT_TAB(i),
TRANSPORT_METHOD_EXT = TRANSPORT_METHOD_EXT_TAB(i),
UOM_CODE = UOM_CODE_TAB(i),
WEIGHT_EXT = WEIGHT_EXT_TAB(i),
WEIGHT_QUALIFIER_EXT = WEIGHT_QUALIFIER_EXT_TAB(i),
WEIGHT_UOM_EXT = WEIGHT_UOM_EXT_TAB(i),
FBO_CONFIGURATION_KEY_1 = FBO_CONFIGURATION_KEY_1_TAB(i),
FBO_CONFIGURATION_KEY_2 = FBO_CONFIGURATION_KEY_2_TAB(i),
FBO_CONFIGURATION_KEY_3 = FBO_CONFIGURATION_KEY_3_TAB(i),
FBO_CONFIGURATION_KEY_4 = FBO_CONFIGURATION_KEY_4_TAB(i),
FBO_CONFIGURATION_KEY_5 = FBO_CONFIGURATION_KEY_5_TAB(i),
MATCH_KEY_ACROSS = MATCH_KEY_ACROSS_TAB(i),
MATCH_KEY_WITHIN = MATCH_KEY_WITHIN_TAB(i),
CRITICAL_KEY_ATTRIBUTES = CRITICAL_KEY_ATTRIBUTES_TAB(i),
ATTRIBUTE_CATEGORY = ATTRIBUTE_CATEGORY_TAB(i),
ATTRIBUTE1 = ATTRIBUTE1_TAB(i),
ATTRIBUTE2 = ATTRIBUTE2_TAB(i),
ATTRIBUTE3 = ATTRIBUTE3_TAB(i),
ATTRIBUTE4 = ATTRIBUTE4_TAB(i),
ATTRIBUTE5 = ATTRIBUTE5_TAB(i),
ATTRIBUTE6 = ATTRIBUTE6_TAB(i),
ATTRIBUTE7 = ATTRIBUTE7_TAB(i),
ATTRIBUTE8 = ATTRIBUTE8_TAB(i),
ATTRIBUTE9 = ATTRIBUTE9_TAB(i),
ATTRIBUTE10 = ATTRIBUTE10_TAB(i),
ATTRIBUTE11 = ATTRIBUTE11_TAB(i),
ATTRIBUTE12 = ATTRIBUTE12_TAB(i),
ATTRIBUTE13 = ATTRIBUTE13_TAB(i),
ATTRIBUTE14 = ATTRIBUTE14_TAB(i),
ATTRIBUTE15 = ATTRIBUTE15_TAB(i),
BLANKET_NUMBER = BLANKET_NUMBER_TAB(i),
INTMED_SHIP_TO_ORG_ID = INTMED_SHIP_TO_ORG_ID_TAB(i),
LAST_UPDATE_DATE = v_last_update_date,
LAST_UPDATED_BY = v_last_updated_by,
LAST_UPDATE_LOGIN = v_last_update_login,
REQUEST_ID = v_request_id,
PROGRAM_APPLICATION_ID = v_program_application_id,
PROGRAM_ID = v_program_id,
PROGRAM_UPDATE_DATE = v_program_update_date,
SHIP_TO_CUSTOMER_ID = SHIP_TO_CUSTOMER_ID_TAB(i)
WHERE header_id = header_id_tab(i)
AND line_id = line_id_tab(i);
rlm_core_sv.dlog(C_DEBUG,'Number of Interface lines updated',SQL%ROWCOUNT);
rlm_message_sv.sql_error('rlm_validateDemand_sv.UpdateInterfaceLines',
v_Progress);
END UpdateInterfaceLines;
PROCEDURE NAME: UpdateInterfaceHeaders
===========================================================================*/
PROCEDURE UpdateInterfaceHeaders
IS
v_Progress VARCHAR(3) := '010';
rlm_core_sv.dpush(C_SDEBUG,'UpdateInterfaceHeaders');
UPDATE rlm_interface_headers SET
CUST_ADDRESS_1_EXT = g_header_rec.CUST_ADDRESS_1_EXT ,
CUST_ADDRESS_2_EXT = g_header_rec. CUST_ADDRESS_2_EXT ,
CUST_ADDRESS_3_EXT = g_header_rec.CUST_ADDRESS_3_EXT ,
CUST_ADDRESS_4_EXT = g_header_rec.CUST_ADDRESS_4_EXT ,
CUST_CITY_EXT = g_header_rec.CUST_CITY_EXT ,
CUST_COUNTRY_EXT = g_header_rec.CUST_COUNTRY_EXT ,
CUST_COUNTY_EXT = g_header_rec.CUST_COUNTY_EXT ,
CUSTOMER_EXT = g_header_rec.CUSTOMER_EXT ,
CUST_NAME_EXT = g_header_rec.CUST_NAME_EXT ,
CUST_POSTAL_CD_EXT = g_header_rec.CUST_POSTAL_CD_EXT ,
CUST_PROVINCE_EXT = g_header_rec.CUST_PROVINCE_EXT ,
CUST_STATE_EXT = g_header_rec.CUST_STATE_EXT ,
CUSTOMER_ID = g_header_rec.CUSTOMER_ID ,
ECE_PRIMARY_ADDRESS_ID = g_header_rec.ECE_PRIMARY_ADDRESS_ID ,
ECE_TP_LOCATION_CODE_EXT = g_header_rec.ECE_TP_LOCATION_CODE_EXT ,
ECE_TP_TRANSLATOR_CODE = g_header_rec.ECE_TP_TRANSLATOR_CODE ,
EDI_CONTROL_NUM_1 = g_header_rec.EDI_CONTROL_NUM_1 ,
EDI_CONTROL_NUM_2 = g_header_rec.EDI_CONTROL_NUM_2 ,
EDI_CONTROL_NUM_3 = g_header_rec.EDI_CONTROL_NUM_3 ,
EDI_TEST_INDICATOR = g_header_rec.EDI_TEST_INDICATOR ,
HEADER_CONTACT_CODE_1 = g_header_rec.HEADER_CONTACT_CODE_1 ,
HEADER_CONTACT_CODE_2 = g_header_rec.HEADER_CONTACT_CODE_2 ,
HEADER_CONTACT_VALUE_1 = g_header_rec.HEADER_CONTACT_VALUE_1 ,
HEADER_CONTACT_VALUE_2 = g_header_rec.HEADER_CONTACT_VALUE_2 ,
HEADER_NOTE_TEXT = g_header_rec.HEADER_NOTE_TEXT ,
HEADER_REF_CODE_1 = g_header_rec.HEADER_REF_CODE_1 ,
HEADER_REF_CODE_2 = g_header_rec.HEADER_REF_CODE_2 ,
HEADER_REF_CODE_3 = g_header_rec.HEADER_REF_CODE_3 ,
HEADER_REF_VALUE_1 = g_header_rec.HEADER_REF_VALUE_1 ,
HEADER_REF_VALUE_2 = g_header_rec.HEADER_REF_VALUE_2 ,
HEADER_REF_VALUE_3 = g_header_rec.HEADER_REF_VALUE_3 ,
PROCESS_STATUS = g_schedule_PS,
SCHEDULE_HEADER_ID = g_header_rec.SCHEDULE_HEADER_ID ,
SCHEDULE_TYPE = g_header_rec.SCHEDULE_TYPE ,
SCHEDULE_TYPE_EXT = g_header_rec.SCHEDULE_TYPE_EXT ,
SCHED_GENERATION_DATE = g_header_rec.SCHED_GENERATION_DATE ,
SCHED_HORIZON_END_DATE = g_header_rec.SCHED_HORIZON_END_DATE ,
SCHED_HORIZON_START_DATE = g_header_rec.SCHED_HORIZON_START_DATE ,
SCHEDULE_PURPOSE = g_header_rec.SCHEDULE_PURPOSE ,
SCHEDULE_PURPOSE_EXT = g_header_rec.SCHEDULE_PURPOSE_EXT ,
SCHEDULE_REFERENCE_NUM = g_header_rec.SCHEDULE_REFERENCE_NUM ,
SCHEDULE_SOURCE = g_header_rec.SCHEDULE_SOURCE ,
LAST_UPDATE_DATE = sysdate,
LAST_UPDATED_BY = fnd_global.user_id ,
CREATION_DATE = g_header_rec.CREATION_DATE ,
CREATED_BY = g_header_rec.CREATED_BY ,
ATTRIBUTE_CATEGORY = g_header_rec.ATTRIBUTE_CATEGORY ,
ATTRIBUTE1 = g_header_rec.ATTRIBUTE1 ,
ATTRIBUTE2 = g_header_rec.ATTRIBUTE2 ,
ATTRIBUTE3 = g_header_rec.ATTRIBUTE3 ,
ATTRIBUTE4 = g_header_rec.ATTRIBUTE4 ,
ATTRIBUTE5 = g_header_rec.ATTRIBUTE5 ,
ATTRIBUTE6 = g_header_rec.ATTRIBUTE6 ,
ATTRIBUTE7 = g_header_rec.ATTRIBUTE7 ,
ATTRIBUTE8 = g_header_rec.ATTRIBUTE8 ,
ATTRIBUTE9 = g_header_rec.ATTRIBUTE9 ,
ATTRIBUTE10 = g_header_rec.ATTRIBUTE10 ,
ATTRIBUTE11 = g_header_rec.ATTRIBUTE11 ,
ATTRIBUTE12 = g_header_rec.ATTRIBUTE12 ,
ATTRIBUTE13 = g_header_rec.ATTRIBUTE13 ,
ATTRIBUTE14 = g_header_rec.ATTRIBUTE14 ,
ATTRIBUTE15 = g_header_rec.ATTRIBUTE15 ,
LAST_UPDATE_LOGIN = fnd_global.login_id ,
REQUEST_ID = fnd_global.conc_REQUEST_ID ,
PROGRAM_APPLICATION_ID = fnd_global.PROG_APPL_ID ,
PROGRAM_ID = fnd_global.conc_PROGRAM_ID ,
PROGRAM_UPDATE_DATE = sysdate
WHERE header_id = g_header_rec.header_id;
rlm_core_sv.dlog(C_DEBUG,'Number of Interface header updated',SQL%ROWCOUNT);
rlm_message_sv.sql_error('rlm_validateDemand_sv.UpdateInterfaceHeaders: ',
v_Progress);
END UpdateInterfaceHeaders;
SELECT COUNT(*)
INTO v_mrp_cnt
FROM rlm_interface_lines
WHERE item_detail_type = '6'
AND header_id = x_header_rec.header_id
AND line_id = x_lines_rec.line_id;
SELECT ETG.TP_GROUP_CODE
INTO x_tp_group_code
FROM ECE_TP_GROUP ETG,
ECE_TP_HEADERS ETH,
HZ_CUST_ACCT_SITES ACCT_SITE
WHERE ETG.TP_GROUP_ID = ETH.TP_GROUP_ID
and ETH.TP_HEADER_ID = ACCT_SITE.TP_HEADER_ID
and ACCT_SITE.CUST_ACCOUNT_ID = x_header_rec.CUSTOMER_ID
and ACCT_SITE.ECE_TP_LOCATION_CODE = x_header_rec.ECE_TP_LOCATION_CODE_EXT;
SELECT ACCOUNT_NUMBER
INTO x_customer_number
FROM HZ_CUST_ACCOUNTS CUST_ACCT
WHERE CUST_ACCT.CUST_ACCOUNT_ID = x_header_rec.Customer_Id;
SELECT ETG.TP_GROUP_CODE
INTO x_tp_group_code
FROM ECE_TP_GROUP ETG,
ECE_TP_HEADERS ETH,
HZ_CUST_ACCT_SITES ACCT_SITE
WHERE ETG.TP_GROUP_ID = ETH.TP_GROUP_ID
and ETH.TP_HEADER_ID = ACCT_SITE.TP_HEADER_ID
and ACCT_SITE.ECE_TP_LOCATION_CODE = x_header_rec.ECE_TP_LOCATION_CODE_EXT;
SELECT ACCOUNT_NUMBER
INTO x_customer_number
FROM HZ_CUST_ACCOUNTS CUST_ACCT
WHERE CUST_ACCT.CUST_ACCOUNT_ID = x_header_rec.Customer_Id;
SELECT 'exists'
INTO v_Temp
FROM HZ_CUST_ACCT_RELATE
WHERE cust_account_id = x_customer_id
AND related_cust_account_id = x_RelatedCustomerId
AND bill_to_flag = 'Y'
AND status='A';
SELECT 'exists'
INTO v_Temp
FROM HZ_CUST_ACCT_RELATE
WHERE cust_account_id = x_customer_id
AND related_cust_account_id = x_RelatedCustomerId
AND ship_to_flag = 'Y'
AND status='A';
SELECT DECODE(order_date_type_code, 'ARRIVAL', 'DELIVER', 'SHIP'),
order_number
INTO v_order_date_type_code, v_order_number
FROM oe_order_headers_all
WHERE header_id = x_line.order_header_id;
SELECT cust_po_number
INTO v_blanket_po
FROM oe_blanket_headers
WHERE order_number = x_Group_rec.setup_terms_rec.blanket_number;
SELECT MIN(start_date_time), MAX(start_date_time)
INTO l_reqdate, h_reqdate
FROM rlm_interface_lines
WHERE header_id = x_Sched_rec.header_id AND
schedule_item_num = x_Group_rec.schedule_item_num AND
item_detail_type IN (k_FIRM, k_FORECAST, k_PAST_DUE_FIRM);
SELECT MIN(start_date_time), MAX(start_date_time)
INTO l_reqdate, h_reqdate
FROM rlm_interface_lines
WHERE header_id = x_Sched_rec.header_id AND
cust_ship_from_org_ext = x_Group_rec.cust_ship_from_org_ext AND
cust_ship_to_ext = x_Group_rec.cust_ship_to_ext AND
customer_item_ext = x_Group_rec.customer_item_ext AND
item_detail_type IN (k_FIRM, k_FORECAST, k_PAST_DUE_FIRM);
SELECT MIN(start_date_time), MAX(start_date_time)
INTO l_reqdate, h_reqdate
FROM rlm_interface_lines
WHERE header_id = x_Sched_rec.header_id AND
ship_from_org_id = x_Group_rec.ship_from_org_id AND
ship_to_address_id = x_Group_rec.ship_to_address_id AND
customer_item_id = x_Group_rec.customer_item_id AND
item_detail_type IN (k_FIRM, k_FORECAST, k_PAST_DUE_FIRM);
SELECT start_date_active, end_date_active, on_hold_flag
INTO v_startdate, v_enddate, v_onholdflag
FROM oe_blanket_headers_ext
WHERE order_number = x_Group_rec.setup_terms_rec.blanket_number;