--Custom table and updates.
CREATE TABLE APPS.XX_SO_INT_GLO
(
SNO NUMBER,
OLD_REF_NUM NUMBER,
ORDER_TYPE VARCHAR2(240 BYTE),
DATE_ORDERED VARCHAR2(240 BYTE),
CUSTOMER_NUMBER VARCHAR2(240 BYTE),
CUSTOMER_NAME VARCHAR2(240 BYTE),
PRICE_LIST VARCHAR2(240 BYTE),
CURRENCY VARCHAR2(240 BYTE),
SOLD_FROM_ORG VARCHAR2(240 BYTE),
SALES_REP VARCHAR2(240 BYTE),
LINE_NUM NUMBER,
INVENTORY_ITEM VARCHAR2(240 BYTE),
ITEM_ID NUMBER,
ORG_ID NUMBER,
ORDERED_QTY NUMBER,
UOM VARCHAR2(240 BYTE),
SELLING_PRICE NUMBER,
EXTRA1 VARCHAR2(240 BYTE),
EXTRA2 VARCHAR2(240 BYTE),
EXTRA3 VARCHAR2(240 BYTE),
STATUS VARCHAR2(240 BYTE),
MESSAGE VARCHAR2(240 BYTE),
SHIP_TO NUMBER,
BILL_TO NUMBER,
CUSTOMER_ID NUMBER,
SALESREP_ID NUMBER,
ORDER_TYPE_ID NUMBER,
PRICE_LIST_ID NUMBER,
WAREHOUSE_ID NUMBER
);
--1 ITEM_ID
UPDATE XX_SO_INT_GLO
SET ITEM_ID = (SELECT INVENTORY_ITEM_ID FROM MTL_SYSTEM_ITEMS_B WHERE SEGMENT1 = INVENTORY_ITEM AND ORGANIZATION_ID = 122)
WHERE ITEM_ID IS NULL
;
COMMIT;
--2 CUSTOMER_ID
UPDATE XX_SO_INT_GLO XX1
SET CUSTOMER_ID =
(SELECT hca.CUST_ACCOUNT_ID
--,HCSA.ORG_ID
FROM HZ_CUST_ACCOUNTS_ALL HCA, hz_cust_acct_sites_all HCSA,HZ_PARTIES HZP
WHERE HCA.CUST_ACCOUNT_ID = HCSA.CUST_ACCOUNT_ID
AND HCSA.ORG_ID = 122
AND HCA.PARTY_ID = HZP.PARTY_ID
AND HCA.ACCOUNT_NUMBER = XX1.CUSTOMER_NUMBER
--AND UPPER(TRIM(CUSTOMER_NAME)) = UPPER(TRIM(PARTY_NAME))
AND BILL_TO_FLAG = 'P'
AND HCSA.STATUS = 'A'
)
WHERE CUSTOMER_ID IS NULL;
--3 SHIP_TO, BILL_TO
UPDATE XX_SO_INT_GLO XX1
SET BILL_TO =
(
SELECT SITE_USE_ID FROM
AR_CUSTOMERS AR, hz_cust_acct_sites_all HZC, hz_cust_site_uses_all HZA
WHERE CUSTOMER_ID = CUST_ACCOUNT_ID
AND HZA.CUST_ACCT_SITE_ID = HZC.CUST_ACCT_SITE_ID
--AND CUSTOMER_ID =8318
--AND CUSTOMER_NUMBER = XX1.CUSTOMER_NUMBER
AND HZC.CUST_ACCOUNT_ID = XX1.CUSTOMER_ID
AND SITE_USE_CODE = 'BILL_TO'
AND BILL_TO_FLAG = 'P'
AND HZC.ORG_ID = 122
AND HZC.STATUS = 'A'
AND HZA.STATUS = 'A'
--AND ROWNUM =1 --NO SITE PROVIDED
)
WHERE BILL_TO IS NULL
--WHERE CUSTOMER_ID = 43050
;
SELECT * FROM XX_SO_INT_GLO WHERE BILL_TO IS NULL AND CUSTOMER_ID IS NOT NULL;
--SHIP TO
UPDATE XX_SO_INT_GLO XX1
SET SHIP_TO =
(
SELECT SITE_USE_ID FROM
AR_CUSTOMERS AR, hz_cust_acct_sites_all HZC, hz_cust_site_uses_all HZA
WHERE CUSTOMER_ID = CUST_ACCOUNT_ID
AND HZA.CUST_ACCT_SITE_ID = HZC.CUST_ACCT_SITE_ID
--AND CUSTOMER_ID =8318
--AND CUSTOMER_NUMBER = XX1.CUSTOMER_NUMBER
AND HZC.CUST_ACCOUNT_ID = XX1.CUSTOMER_ID
AND SITE_USE_CODE = 'SHIP_TO'
AND BILL_TO_FLAG = 'P'
AND HZC.ORG_ID = 122
AND HZC.STATUS = 'A'
AND HZA.STATUS = 'A'
--AND ROWNUM =1 --NO SITE PROVIDED
)
--WHERE CUSTOMER_ID = 43050
WHERE SHIP_TO IS NULL
;
COMMIT;
SELECT DISTINCT CUSTOMER_NAME FROM XX_SO_INT_GLO WHERE (SHIP_TO IS NULL OR BILL_TO IS NULL) AND CUSTOMER_ID IS NOT NULL;
SELECT * FROM XX_SO_INT_GLO;
--4 order_type_id
SELECT NAME, OTT.TRANSACTION_TYPE_ID FROM
oe_transaction_types_all ott
,oe_transaction_types_tl ottl
WHERE OTT.TRANSACTION_TYPE_ID = OTTL.TRANSACTION_TYPE_ID
AND OTT.TRANSACTION_TYPE_ID = 1021;
UPDATE XX_SO_INT_GLO
SET order_type_id =
(SELECT OTT.TRANSACTION_TYPE_ID FROM
oe_transaction_types_all ott
,oe_transaction_types_tl ottl
WHERE OTT.TRANSACTION_TYPE_ID = OTTL.TRANSACTION_TYPE_ID
AND UPPER(NAME) = UPPER(ORDER_TYPE));
COMMIT;
SELECT * FROM XX_SO_INT_GLO
WHERE order_type_id IS NULL;
WHERE ORDER_TYPE LIKE '%Return%';
WHERE ORDER_TYPE_ID IS NULL;
--5 PRICE_LIST_ID
SELECT LIST_HEADER_ID, NAME FROM qp_secu_list_headers_v ;
UPDATE XX_SO_INT_GLO
SET PRICE_LIST_ID =
(SELECT LIST_HEADER_ID FROM qp_secu_list_headers_v
WHERE UPPER(NAME) = UPPER(price_list)
)
WHERE PRICE_LIST_ID IS NULL;
COMMIT;
SELECT DISTINCT PRICE_LIST FROM XX_SO_INT_GLO WHERE PRICE_LIST_ID IS NULL;
SELECT * FROM XX_SO_INT_GLO WHERE PRICE_LIST_ID IS NULL;
--6 salesrep_id
--dont use below query, search for the salesperson_id and update manual
UPDATE XX_SO_INT_GLO XX1
SET SALESREP_ID =
(
SELECT HZA.PRIMARY_SALESREP_ID FROM
AR_CUSTOMERS AR, hz_cust_acct_sites_all HZC, hz_cust_site_uses_all HZA
WHERE CUSTOMER_ID = CUST_ACCOUNT_ID
AND HZA.CUST_ACCT_SITE_ID = HZC.CUST_ACCT_SITE_ID
--AND CUSTOMER_ID =8318
AND CUSTOMER_NUMBER = XX1.CUSTOMER_NUMBER
AND SITE_USE_CODE = 'BILL_TO'
AND BILL_TO_FLAG = 'P'
AND HZC.ORG_ID = 121
AND HZC.STATUS = 'A'
AND HZA.STATUS = 'A'
--AND ROWNUM = 1 --NO SITE PROVIDED
)
WHERE SALESREP_ID IS NULL
;
SELECT JTF.SALESREP_ID,NAME,
SALESREP_NUMBER,
SOURCE_EMAIL,
SOURCE_JOB_TITLE,
SOURCE_ADDRESS1,
SOURCE_MGR_NAME,
SOURCE_MOBILE_PHONE,
JTF.SALESREP_ID
FROM jtf_rs_salesreps JTF, JTF_RS_RESOURCE_EXTNS_VL JTFRES
WHERE JTF.RESOURCE_ID = JTFRES.RESOURCE_ID
AND UPPER(NAME) like UPPER('%SS%');
--AND JTF.SALESREP_ID =HZCU.PRIMARY_SALESREP_ID
SELECT DISTINCT SALES_REP FROM XX_SO_INT_GLO XX1
WHERE SALESREP_ID IS NULL;
UPDATE XX_SO_INT_GLO XX1
SET SALESREP_ID = 100000067
WHERE SALES_REP = 'Mr.Afzal';
COMMIT;
SELECT * FROM XX_SO_INT_GLO WHERE SALESREP_ID IS NULL AND CUSTOMER_ID IS NOT NULL;
--7 item exists in price_list NOT NEEDED
SELECT * FROM qp_list_lines_v;
SELECT OLD_REF_NUM,SELLING_PRICE,INVENTORY_ITEM, PRICE_LIST_ID, (SELECT OPERAND FROM qp_list_lines_v WHERE PRODUCT_ATTR_VALUE = ITEM_ID AND LIST_HEADER_ID = PRICE_LIST_ID)SELLING_PRICE_LSIT
FROM XX_SO_INT_GLO
WHERE OLD_REF_NUM NOT IN
(SELECT ORDER_NUMBER FROM OE_ORDER_HEADERS_ALL)
AND ITEM_ID IS NOT NULL
-- AND OLD_REF_NUM IN (4800, 4830) --5022 --5090
-- AND CURRENCY = 'AED'
-- ORDER BY 2
;
--8 check the price list price and given price --not required, we are passing manual amt
SELECT OLD_REF_NUM,SELLING_PRICE,INVENTORY_ITEM, PRICE_LIST_ID, OPERAND
--(SELECT OPERAND FROM qp_list_lines_v WHERE PRODUCT_ATTR_VALUE = ITEM_ID AND LIST_HEADER_ID = PRICE_LIST_ID)SELLING_PRICE_LSIT
FROM XX_SO_INT_GLO XX1, qp_list_lines_v QPV
WHERE OLD_REF_NUM NOT IN
(SELECT ORDER_NUMBER FROM OE_ORDER_HEADERS_ALL)
AND XX1.ITEM_ID = QPV.PRODUCT_ATTR_VALUE
AND LIST_HEADER_ID = PRICE_LIST_ID
AND SELLING_PRICE <> OPERAND
-- AND OLD_REF_NUM IN (4800, 4830) --5022 --5090
-- AND CURRENCY = 'AED'
ORDER BY 2
;
--8 UPDATE PAYMENT_TERMS
UPDATE XX_SO_INT_GLO
SET TERMS_ID = (SELECT TERM_ID FROM RA_TERMS WHERE UPPER(TRIM(PAY_TERMS)) = UPPER(TRIM(NAME)))
WHERE TERMS_ID IS NULL
;
commit;
SELECT DISTINCT PAY_TERMS, TERMS_ID from XX_SO_INT_GLO where terms_id is null;
UPDATE
XX_SO_INT_GLO
SET TERMS_ID = 1009
WHERE TERMS_ID IS NULL
AND PAY_TERMS LIKE '10%AB90D-PDCOBD';
;
SELECT DISTINCT PAY_TERMS FROM XX_SO_INT_GLO
WHERE TERMS_ID IS NULL
;
UPDATE XX_SO_INT_GLO
SET TERMS_ID =5
WHERE TERMS_ID IS NULL;
SELECT * FROM XX_SO_INT_GLO
WHERE TERMS_ID IS NULL
;
SELECT TERM_ID, NAME FROM RA_TERMS-- WHERE TERMS_ID IS NULL
WHERE NAME like '10%B%';
--2 Interface Tables of SO inserting data.
/*
delete from OE_HEADERS_IFACE_ALL;
delete from OE_LINES_IFACE_ALL;
delete FROM OE_ACTIONS_IFACE_ALL;
delete FROM OE_PROCESSING_MSGS_TL;
delete FROM OE_PROCESSING_MSGS;
select * from OE_PROCESSING_MSGS_TL;
select * from OE_PROCESSING_MSGS;
select *
--delete
from OE_HEADERS_IFACE_ALL;
edit
OE_HEADERS_IFACE_ALL;
--to process errored lines
UPDATE OE_HEADERS_IFACE_ALL
SET REQUEST_ID = NULL, ERROR_FLAG = NULL, XML_MESSAGE_ID = NULL
--to check error lines with item_id
select *--distinct ORIG_SYS_DOCUMENT_REF
--delete
from OE_LINES_IFACE_ALL
WHERE ORIG_SYS_LINE_REF IN (SELECT ORIGINAL_SYS_DOCUMENT_LINE_REF
FROM
OE_PROCESSING_MSGS
where type = 'ERROR' )
;
edit
--select distinct ORIG_SYS_DOCUMENT_REF, line_number
OE_LINES_IFACE_ALL
order by ORIG_SYS_DOCUMENT_REF, LINE_NUMBER
;
SELECT *
--delete
FROM OE_ACTIONS_IFACE_ALL;
SELECT *
--delete
FROM
OE_PROCESSING_MSGS
where type = 'ERROR'
--ORDER BY CREATION_DATE DESC
;
SELECT *
--delete
FROM
OE_PROCESSING_MSGS_TL
WHERE TRANSACTION_ID IN ( SELECT TRANSACTION_ID FROM
OE_PROCESSING_MSGS
where type = 'ERROR'
--AND TRANSACTION_ID IN (22363,22376,22375)
)
ORDER BY CREATION_DATE DESC;
SELECT * FROM OE_ORDER_HEADERS_ALL
WHERE LAST_UPDATE_DATE LIKE SYSDATE;
--workflow status ENTERED, BOOKED ..etc
SELECT *
FROM fnd_lookup_values
WHERE lookup_type = 'LINE_FLOW_STATUS'
AND language = 'US';
SELECT *
FROM fnd_lookup_values
WHERE lookup_type = 'TRIP_STATUS'
AND language = 'US';
*/
--CREATE TABLE XX_SO_INT_GLO_BKUP2
--AS SELECT * FROM XX_SO_INT_GLO;
--
--DELETE FROM XX_SO_INT_GLO;
--for return type Order_type use return_reason = 'NO REASON' ex: Return Material UV - Direct CM
DECLARE
l_org_id hr_operating_units.organization_id%TYPE := 122;
PRICE_LIST_ID qp_list_headers_tl.list_header_id%TYPE;
l_verify_flag CHAR (1);
l_error_message VARCHAR2 (3000);
termS_id ra_terms_tl.term_id%TYPE;
ORDER_TYPE_ID oe_transaction_types_tl.transaction_type_id%TYPE;
l_order_source_id oe_order_sources.order_source_id%TYPE := 0; --Online default
l_inventory_item_id mtl_system_items_b.inventory_item_id%TYPE;
l_organization_id org_organization_definitions.organization_id%TYPE;
l_uom_code mtl_units_of_measure_vl.uom_code%TYPE;
l_line_type_id oe_transaction_types_tl.transaction_type_id%TYPE;
l_currency_code FND_CURRENCIES_VL.currency_code%TYPE;
l_customer_id ra_customers.customer_id%TYPE;
l_invoice_to_orgid oe_invoice_to_orgs_v.organization_id%TYPE;
l_shipto_org_id oe_ship_to_orgs_v.organization_id%TYPE;
l_user_id fnd_user.user_id%TYPE;
l_ship_to_org_id NUMBER (10);
l_bill_to_org_id NUMBER (10);
L_OLD_REF_NUM NUMBER := 0;
/*
UPDATE OE_ORDER_LINES OEL
SET SHIP_FROM_ORG_ID = 121--'SGL'
WHERE OEL.HEADER_ID IN (SELECT OEH.HEADER_ID FROM OE_ORDER_HEADERS OEH
WHERE order_type_id = 1321 and oeh.org_id = 121)
AND SHIP_FROM_ORG_ID IS NULL
AND LAST_UPDATE_DATE LIKE SYSDATE --= 1170
;
*/
--global
--total 151
--exists 52
--new 99
--llc
--total 54
--exists 21
--new 33
CURSOR c_header IS
--if order_type like '%Return%' then add ship_from_org_id = org_id (121/122/123) header and lines use above script later if forgot
SELECT *--distinct old_ref_num
--SELECT DISTINCT OLD_REF_NUM--, LINE_NUM
--select distinct order_type
FROM XX_SO_INT_GLO
-- WHERE ORDER_TYPE LIKE 'Return Material%'
WHERE --OLD_REF_NUM IN ( '5639') AND
OLD_REF_NUM not IN (SELECT ORDER_NUMBER
FROM OE_ORDER_HEADERS_ALL --where creation_date like sysdate
WHERE ORG_ID = 122)
-- AND QUOTE_DATE IS NULL
AND DATE_ORDERED IS NOT NULL
-- AND ITEM_ID IS NULL
-- and header_STATUS IN ('BOOKED','CLOSED','ENTERED')
-- AND LINE_STATUS <> 'CANCELLED'
-- and order_type like '%Return%'
-- AND OLD_REF_NUM = '5186'
-- AND NVL (verify_flag, 'N') = 'N'
-- AND CURRENCY = 'AED'
-- AND HEADER_STATUS = 'BOOKED'
-- AND LINE_STATUS <> 'CANCELLED'
-- ORDER BY 2,3;
ORDER BY OLD_REF_NUM, LINE_NUM;
-- EDIT XX_SO_INT_GLO WHERE OLD_REF_NUM IN ( '831' ,'5857') AND header_STATUS IN ('BOOKED','CLOSED') AND LINE_STATUS <> 'CANCELLED' ORDER BY LINE_NUM
/* CURSOR c_lines (P_OLD_REF_NUM VARCHAR2)
IS
update XX_SO_INT_GLO
set line_num = sno
where old_ref_num = 5251
SELECT *
FROM XX_SO_INT_GLO
where old_ref_num = 5251;
*/
BEGIN
FOR h1 IN c_header
LOOP
l_error_message := NULL;
l_verify_flag := 'Y';
IF L_OLD_REF_NUM <> h1.OLD_REF_NUM
THEN
BEGIN
L_OLD_REF_NUM := h1.OLD_REF_NUM;
--savepoint a;
INSERT INTO oe_headers_iface_all (order_source_id,
orig_sys_document_ref,
order_number,
--quote_number,
org_id,
sold_from_org_id --,ship_from_org_id
,
ordered_date,
--quote_date,
order_type_id,
sold_to_org_id,
payment_term_id,
operation_code,
created_by,
creation_date,
last_updated_by,
last_update_date,
customer_po_number,
price_list_id --,context
--,attribute19
,
ship_to_org_id,
invoice_to_org_id,
salesrep_id-- ,booked_flag
,ATTRIBUTE1
,ATTRIBUTE4
,TRANSACTIONAL_CURR_CODE
,FREIGHT_TERMS
--,RETURN_REASON_CODE
)
VALUES (l_order_source_id --order_source_id
,
oe_order_headers_s.NEXTVAL --orig_sys_document_ref
,
h1.OLD_REF_NUM, --order_number / QuoteNumber
--h1.OLD_REF_NUM, --quote_number
l_org_id --org_id
,
l_org_id --sold_from_org_id
--,l_organization_id --ship_from_org_id
,NVL (TO_DATE (h1.DATE_ORDERED, 'DD-Mon-YYYY'),TO_DATE (h1.CREATION_DATE, 'DD-Mon-YYYY')) --ordered_date
--,TO_DATE (h1.QUOTE_DATE, 'DD-MON-YYYY')
,
h1.order_type_id --order_type_id
,
h1.customer_id --sold_to_org_id
,
h1.terms_id --payment_term_id
,
oe_globals.g_opr_create -- 'CREATE' --operation_code
,
-1 --created_by
,
SYSDATE --creation_date
,
-1 --last_updated_by
,
SYSDATE --last_update_date
,
NULL --customer_po_number
,
h1.price_list_id --price_list_id
--,'xxx Legacy SO Number'
--,trim(h1.legacy_so_num)
,
h1.SHIP_TO,
h1.BILL_TO,
NVL (h1.salesrep_id, -3)-- ,'Y'
,H1.ENQ_NO_DFF --||' '||H1.NEW_DESCRIPTION_DFF
,H1.CUSTOMER_NAME_DFF
,h1.currency
,'EXWORK'
--,'NO REASON'
);
EXCEPTION
WHEN OTHERS
THEN
l_error_message := SQLERRM;
l_verify_flag := 'N';
UPDATE XX_SO_INT_GLO
SET verify_flag = 'N',
error_message = 'Header error' || l_error_message
WHERE customer_id = h1.customer_id
AND OLD_REF_NUM = h1.OLD_REF_NUM;
--goto next_so;
END;
------------------Line Details ---------------------
/*INSERT INTO OE_ACTIONS_IFACE_ALL (
order_source_id
,orig_sys_document_ref
,operation_code
)
VALUES (
l_order_source_id --order_source_id
, oe_order_headers_s.currVAL--orig_sys_document_ref
,'ENTERED'--'DRAFT'--'BOOK_ORDER' --operation_code
);*/
-- commit;
END IF;
BEGIN
INSERT INTO oe_lines_iface_all (order_source_id,
orig_sys_document_ref,
orig_sys_line_ref,
line_number,
inventory_item_id,
ordered_quantity --,ship_from_org_id
,
org_id,
pricing_quantity,
unit_selling_price,
unit_list_price,
price_list_id,
payment_term_id --,schedule_ship_date
,
created_by,
creation_date,
last_updated_by,
last_update_date --,line_type_id
,
calculate_price_flag
-- ,REQUEST_DATE ----,request_date
---- ,OPERATION_CODE
-- --,SCHEDULE_SHIP_DATE
-- ,SCHEDULE_DATE
-- ,SCHEDULE_ARRIVAL_DATE
-- , PROMISE_DATE
)
VALUES (l_order_source_id --order_source_id
,
oe_order_headers_s.CURRVAL --orig_sys_document_ref
,
oe_order_lines_s.NEXTVAL --orig_sys_line_ref
,
TRIM (h1.line_num) --line_number
,
h1.item_id --inventory_item_id
,
TRIM (h1.ordered_qty) --ordered_quantity
--,l_organization_id --ship_from_org_id
,
l_org_id --ship_from_org_id
,
TRIM (h1.ordered_qty) --pricing_quantity
,
TRIM (h1.selling_price) --unit_selling_price
,
TRIM (h1.selling_price) --unit_list_price
,
h1.price_list_id --price_list_id
,
h1.terms_id --payment_term_id
--,to_date(h1.scheduled_ship_date,'DD-MON-RRRR') --schedule_ship_date
--,to_date(h1.request_date,'DD-MON-RRRR') --request_date
,
-1 --created_by
,
SYSDATE --creation_date
,
-1 --last_updated_by
,
SYSDATE --last_update_date
--,l_line_type_id --line_type_id
,
'N'
-- TO_DATE (h1.QUOTE_DATE, 'DD-MON-YYYY') --REQUEST_DATE
---- ,H1.LINE_STATUS --OPEATION_CODE
-- ,NVL(TO_DATE(H1.SCHEDULE_SHIP_DATE, 'DD-MON-YYYY'),TO_DATE (h1.DATE_ORDERED, 'DD-MON-YYYY'))
-- ,NVL(TO_DATE(H1.SCHEDULE_SHIP_DATE, 'DD-MON-YYYY'),TO_DATE (h1.DATE_ORDERED, 'DD-MON-YYYY'))
-- ,NVL(TO_DATE(H1.SCHEDULE_SHIP_DATE, 'DD-MON-YYYY'),TO_DATE (h1.DATE_ORDERED, 'DD-MON-YYYY'))
);
UPDATE XX_SO_INT_GLO
SET verify_flag = 'Y'
WHERE customer_id = h1.customer_id
AND OLD_REF_NUM = h1.OLD_REF_NUM
AND item_id = h1.item_Id;
EXCEPTION
WHEN OTHERS
THEN
ROLLBACK TO a;
l_error_message := SQLERRM;
UPDATE XX_SO_INT_GLO
SET verify_flag = 'N',
error_message = 'Line error' || l_error_message
WHERE customer_id = h1.customer_id
AND OLD_REF_NUM = h1.OLD_REF_NUM
AND item_id = h1.item_Id;
END;
COMMIT;
END LOOP;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.PUT_LINE (SQLERRM);
END;
commit;
--3 running the concurrent program.
Order Import --> concurrent Program
Parameters:
Operaring Unit: <select_OU>
Validate Only? No
Instances: 4
Trim Trailing Blanks: No
Validate Descriptive Flexfield: No
--Other parameters leave blank
--4 api to process ENTERED to BOOKED
DECLARE
v_api_version_number NUMBER := 1;
v_return_status VARCHAR2 (2000);
v_msg_count NUMBER;
v_msg_data VARCHAR2 (2000);
-- IN Variables --
v_header_rec oe_order_pub.header_rec_type;
v_line_tbl oe_order_pub.line_tbl_type;
v_action_request_tbl oe_order_pub.request_tbl_type;
v_line_adj_tbl oe_order_pub.line_adj_tbl_type;
-- OUT Variables --
v_header_rec_out oe_order_pub.header_rec_type;
v_header_val_rec_out oe_order_pub.header_val_rec_type;
v_header_adj_tbl_out oe_order_pub.header_adj_tbl_type;
v_header_adj_val_tbl_out oe_order_pub.header_adj_val_tbl_type;
v_header_price_att_tbl_out oe_order_pub.header_price_att_tbl_type;
v_header_adj_att_tbl_out oe_order_pub.header_adj_att_tbl_type;
v_header_adj_assoc_tbl_out oe_order_pub.header_adj_assoc_tbl_type;
v_header_scredit_tbl_out oe_order_pub.header_scredit_tbl_type;
v_header_scredit_val_tbl_out oe_order_pub.header_scredit_val_tbl_type;
v_line_tbl_out oe_order_pub.line_tbl_type;
v_line_val_tbl_out oe_order_pub.line_val_tbl_type;
v_line_adj_tbl_out oe_order_pub.line_adj_tbl_type;
v_line_adj_val_tbl_out oe_order_pub.line_adj_val_tbl_type;
v_line_price_att_tbl_out oe_order_pub.line_price_att_tbl_type;
v_line_adj_att_tbl_out oe_order_pub.line_adj_att_tbl_type;
v_line_adj_assoc_tbl_out oe_order_pub.line_adj_assoc_tbl_type;
v_line_scredit_tbl_out oe_order_pub.line_scredit_tbl_type;
v_line_scredit_val_tbl_out oe_order_pub.line_scredit_val_tbl_type;
v_lot_serial_tbl_out oe_order_pub.lot_serial_tbl_type;
v_lot_serial_val_tbl_out oe_order_pub.lot_serial_val_tbl_type;
v_action_request_tbl_out oe_order_pub.request_tbl_type;
CURSOR C1 IS
--
--UPDATE OE_ORDER_LINES_ALL
--SET PRICE_LIST_ID = 11019
--WHERE HEADER_ID IN (SELECT HEADER_ID FROM
SELECT * FROM
OE_ORDER_HEADERS_ALL
WHERE
FLOW_STATUS_CODE = 'ENTERED' And
ORG_ID = 122
AND ORDER_NUMBER <> '6952'
AND LAST_UPDATE_DATE LIKE SYSDATE
AND ORDER_NUMBER IN (SELECT OLD_REF_NUM FROM XX_SO_INT_GLO
--WHERE header_STATUS IN ('BOOKED','CLOSED')
--AND ORDER_TYPE LIKE 'Return Material%'
)
--AND CREATED_BY = 1170
;
--wait 1 min after script is run, to check.
BEGIN
DBMS_OUTPUT.PUT_LINE('Starting of script');
-- Setting the Enviroment --
mo_global.init('ONT');
fnd_global.apps_initialize ( user_id => 1170
,resp_id => 50958 --LLOC --GLOBAL 50957
,resp_appl_id => 660);
mo_global.set_policy_context('S',122);
FOR I IN C1 LOOP
v_action_request_tbl (1) := oe_order_pub.g_miss_request_rec;
v_action_request_tbl(1).request_type := OE_GLOBALS.G_BOOK_ORDER;
v_action_request_tbl(1).entity_code := OE_GLOBALS.G_ENTITY_HEADER;
v_action_request_tbl(1).entity_id := I.HEADER_ID; --pass header_id here
----DBMS_OUTPUT.PUT_LINE('Starting of API');
-- Calling the API to to Book an Existing Order --
BEGIN
OE_ORDER_PUB.PROCESS_ORDER (
p_api_version_number => v_api_version_number
, p_header_rec => v_header_rec
, p_line_tbl => v_line_tbl
, p_action_request_tbl => v_action_request_tbl
, p_line_adj_tbl => v_line_adj_tbl
-- OUT variables
, x_header_rec => v_header_rec_out
, x_header_val_rec => v_header_val_rec_out
, x_header_adj_tbl => v_header_adj_tbl_out
, x_header_adj_val_tbl => v_header_adj_val_tbl_out
, x_header_price_att_tbl => v_header_price_att_tbl_out
, x_header_adj_att_tbl => v_header_adj_att_tbl_out
, x_header_adj_assoc_tbl => v_header_adj_assoc_tbl_out
, x_header_scredit_tbl => v_header_scredit_tbl_out
, x_header_scredit_val_tbl => v_header_scredit_val_tbl_out
, x_line_tbl => v_line_tbl_out
, x_line_val_tbl => v_line_val_tbl_out
, x_line_adj_tbl => v_line_adj_tbl_out
, x_line_adj_val_tbl => v_line_adj_val_tbl_out
, x_line_price_att_tbl => v_line_price_att_tbl_out
, x_line_adj_att_tbl => v_line_adj_att_tbl_out
, x_line_adj_assoc_tbl => v_line_adj_assoc_tbl_out
, x_line_scredit_tbl => v_line_scredit_tbl_out
, x_line_scredit_val_tbl => v_line_scredit_val_tbl_out
, x_lot_serial_tbl => v_lot_serial_tbl_out
, x_lot_serial_val_tbl => v_lot_serial_val_tbl_out
, x_action_request_tbl => v_action_request_tbl_out
, x_return_status => v_return_status
, x_msg_count => v_msg_count
, x_msg_data => v_msg_data
);
EXCEPTION WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SQLERRM);
END;
DBMS_OUTPUT.PUT_LINE('Completion of API');
IF v_return_status = fnd_api.g_ret_sts_success THEN
COMMIT;
DBMS_OUTPUT.put_line ('Booking of an Existing Order is Success ');
ELSE
DBMS_OUTPUT.put_line ('Booking of an Existing Order failed:'||v_msg_data);
ROLLBACK;
FOR i IN 1 .. v_msg_count
LOOP
v_msg_data := oe_msg_pub.get( p_msg_index => i, p_encoded => 'F');
dbms_output.put_line( i|| ') '|| v_msg_data);
END LOOP;
END IF;
END LOOP;
END;
COMMIT;
--5 Query:
SELECT
ORDER_NUMBER,
(SELECT NAME--, OTT.TRANSACTION_TYPE_ID
FROM
oe_transaction_types_all ott
,oe_transaction_types_tl ottl
WHERE OTT.TRANSACTION_TYPE_ID = OTTL.TRANSACTION_TYPE_ID
AND OTT.TRANSACTION_TYPE_ID = OEH.ORDER_TYPE_ID
AND ORDER_TYPE_ID = OEH.ORDER_TYPE_ID
)ORDER_TYPE,
ORDERED_DATE,
(SELECT CUSTOMER_NUMBER FROM AR_CUSTOMERS WHERE CUSTOMER_ID =OEH.SOLD_TO_ORG_ID) CUSTOMER_NUMBER,
--OEH.SOLD_TO_ORG_ID,
TRANSACTIONAL_CURR_CODE,
NVL((SELECT NAME FROM jtf_rs_salesreps JTF, JTF_RS_RESOURCE_EXTNS_VL JTFRES
WHERE JTF.RESOURCE_ID = JTFRES.RESOURCE_ID
AND OEH.SALESREP_ID = JTF.SALESREP_ID
AND ORG_ID = OEH.ORG_ID),OEH.SALESREP_ID)SALESREP_NAME,
(SELECT NAME FROM qp_secu_list_headers_v
WHERE LIST_HEADER_ID = OEH.PRICE_LIST_ID)PRICE_LIST_NAME,
LINE_NUMBER,
(SELECT SEGMENT1 FROM MTL_SYSTEM_ITEMS_B WHERE INVENTORY_ITEM_ID = OEL.INVENTORY_ITEM_ID AND ORGANIZATION_ID = OEH.ORG_ID)ITEM_CODE
,ORDERED_QUANTITY,
ORDER_QUANTITY_UOM,unit_selling_price
,OEH.flow_status_code HEADER_STATUS
,OEH.ORG_ID
FROM OE_ORDER_HEADERS_ALL OEH, OE_ORDER_LINES_ALL OEL
WHERE --OEH.CREATION_DATE LIKE SYSDATE
OEH.HEADER_ID = OEL.HEADER_ID
ORDER BY ORDER_NUMBER, LINE_NUMBER
;
No comments:
Post a Comment