SELECT * FROM XX_PO_INT_TEST;
CREATE TABLE APPS.XX_PO_INT_TEST
(
SNO VARCHAR2(30 BYTE), --for tech ref to capture in line_attribute15
OLD_REF_NUM VARCHAR2(240 BYTE),
VENDOR_NUMBER VARCHAR2(30 BYTE),
PO_DATE VARCHAR2(30 BYTE),
NEED_BY_DATE VARCHAR2(30 BYTE),
SUPPLIER_NAME VARCHAR2(240 BYTE), --optional
SITE_NAME VARCHAR2(240 BYTE),
SHIP_TO VARCHAR2(240 BYTE), --static 142 for global 202 for lifeco
BILL_TO VARCHAR2(240 BYTE), --static 142 for global 202 for lifeco
VENDOR_ID NUMBER,
VENDOR_SITE_ID NUMBER,
SHIP_TO_LOCATION_ID NUMBER, --static 142 for global 202 for lifeco
BILL_TO_LOCATION_ID NUMBER, --static 142 for global 202 for lifeco
CURRENCY VARCHAR2(240 BYTE),
ITEM_CODE VARCHAR2(240 BYTE),
ITEM_ID NUMBER,
LINE_NUM NUMBER,
UNIT_OF_MEASURE VARCHAR2(240 BYTE),
QUANTITY NUMBER,
UNIT_PRICE NUMBER,
EXTRA1 VARCHAR2(240 BYTE),
EXTAR2 VARCHAR2(240 BYTE),
UOM_CODE VARCHAR2(240 BYTE)
);
SELECT * FROM
UPDATE XX_PO_INT_LLC
SET PO_DATE = '14-OCT-2023';
--delete from XX_PO_INT_LLC;
bill_to_location_id: 202
ship_to_location_id: 202
--delete from XX_PO_INT_TEST;
commit;
--1 AGENT_ID
SELECT EMPLOYEE_ID, USER_ID FROM FND_USER WHERE USER_NAME = 'SF-1326'; --599 person_id, 1337 USER_ID
--2 vendor_id
select * from XX_PO_INT_LLC;
UPDATE XX_PO_INT_LLC
SET VENDOR_ID = (SELECT DISTINCT AP.VENDOR_ID
FROM AP_SUPPLIERS AP, AP_SUPPLIER_SITES_ALL APSA
WHERE AP.VENDOR_ID = APSA.VENDOR_ID
AND ORG_ID = 122
AND SEGMENT1 = supplier_name --VENDOR_NUMBER
)
WHERE VENDOR_ID IS NULL;
SELECT * FROM XX_PO_INT_LLC WHERE VENDOR_ID IS NULL;
--3 supplier site vendor_site_id
---checking if sites more than 1 then ask the user to give exact site name
SELECT DISTINCT VENDOR_NUMBER, SUPPLIER_NAME,OLD_REF_NUM
FROM XX_PO_INT_TEST
WHERE VENDOR_ID in(
SELECT AP.VENDOR_ID--,SEGMENT1, COUNT( AP.VENDOR_ID)--, APSA.VENDOR_SITE_ID
FROM AP_SUPPLIERS AP, AP_SUPPLIER_SITES_ALL APSA WHERE AP.VENDOR_ID = APSA.VENDOR_ID
AND AP.VENDOR_ID IN (SELECT VENDOR_ID FROM XX_PO_INT_TEST)
AND ORG_ID = 122
AND END_DATE_ACTIVE IS NULL
AND APSA.INACTIVE_DATE IS NULL
GROUP BY AP.VENDOR_ID,SEGMENT1
HAVING COUNT(AP.VENDOR_ID) >1
)
ORDER BY 1
;
UPDATE XX_PO_INT_LLC XX
SET VENDOR_SITE_ID = (SELECT VENDOR_SITE_ID FROM
AP_SUPPLIERS APS,
AP_SUPPLIER_SITES_ALL APSA
WHERE ORG_ID = 122
AND END_DATE_ACTIVE IS NULL
AND APSA.INACTIVE_DATE IS NULL
AND APSA.VENDOR_ID = APS.VENDOR_ID
AND APS.VENDOR_ID = XX.VENDOR_ID
AND VENDOR_SITE_CODE = SITE
)
-- AND EXTRA1 <> 'Ningbo Jintian Fire Fighting intl.Co.Ltd.'
WHERE VENDOR_ID IS NOT NULL
AND VENDOR_SITE_ID IS NULL;
--AND VENDOR_NUMBER NOT IN (SELECT VENDOR_NUMBER FROM XXSUP_SITES); --XXSUP_SITES we got the supplier sites sepeartely
UPDATE XX_PO_INT_TEST
SET VENDOR_SITE_ID = (SELECT VENDOR_SITE_ID FROM
AP_SUPPLIERS APS,
AP_SUPPLIER_SITES_ALL APSA
WHERE VENDOR_NUMBER = SEGMENT1
AND ORG_ID = 121
AND END_DATE_ACTIVE IS NULL
AND APSA.INACTIVE_DATE IS NULL
AND APSA.VENDOR_ID = APS.VENDOR_ID
AND VENDOR_SITE_CODE = SITE_NAME
)
-- AND EXTRA1 <> 'Ningbo Jintian Fire Fighting intl.Co.Ltd.'
WHERE VENDOR_ID IS NOT NULL
AND VENDOR_SITE_ID IS NULL
AND VENDOR_NUMBER IN (SELECT VENDOR_NUMBER FROM XXSUP_SITES);
--4 ship_to_location_id optinal
--142 DEFAULT
--SHIP_TO, BILL_TO
SELECT * FROM XX_PO_INT_LLC WHERE VENDOR_SITE_ID IS NULL;
UPDATE XX_PO_INT_LLC
SET BILL_TO_LOCATION_ID = 202,-- 142 GLOBAL, 202 LLC
SHIP_TO_LOCATION_ID = 202;
SELECT
APSA.*,
AP.SEGMENT1, AP.VENDOR_NAME
, AP.VENDOR_ID
, APSA.VENDOR_SITE_CODE
, APSA.VENDOR_SITE_ID
FROM AP_SUPPLIERS AP , AP_SUPPLIER_SITES_ALL APSA
WHERE AP.VENDOR_ID = APSA.VENDOR_ID AND ORG_ID = 121
;
--5 bill_to_location_id --optinal
--6 item_id
UPDATE XX_PO_INT_LLC
SET ITEM_ID = (SELECT INVENTORY_ITEM_ID
FROM MTL_SYSTEM_ITEMS_B
WHERE SEGMENT1 = ITEM_CODE AND ORGANIZATION_ID = 122)
WHERE ITEM_ID IS NULL
;
SELECT * FROM XX_PO_INT_TEST
WHERE ITEM_ID IS NULL;
UPDATE XX_PO_INT_TEST
SET ITEM_CODE = '06160173002020004'
WHERE ITEM_CODE = '061660173002020004';
--7 unit_of_measure test all are availble r not in the masters.
SELECT DISTINCT UOM_CODE FROM XX_PO_INT_TEST;
--dont update working fine without uom_code update
-- UPDATE XX_PO_INT_TEST
-- SET UOM_CODE = (SELECT PRIMARY_UOM_CODE FROM MTL_SYSTEM_ITEMS_B WHERE INVENTORY_ITEM_ID = ITEM_ID AND ORGANIZATION_ID = 121);
SELECT DISTINCT PRIMARY_UOM_CODE FROM MTL_SYSTEM_ITEMS_B WHERE ORGANIZATION_ID = 121;
SELECT SEGMENT1, PRIMARY_UOM_CODE FROM MTL_SYSTEM_ITEMS_B WHERE INVENTORY_ITEM_ID = 20290;
SELECT * FROM XX_PO_INT_TEST
WHERE ITEM_CODE= '06175181001020001' ;
--8 TERMS --optinal will get default from the supplier
SELECT TERM_ID,NAME FROM AP_TERMS;
--9 item attributes
UPDATE MTL_SYSTEM_ITEMS_B
SET PURCHASING_ENABLED_FLAG = 'Y', PURCHASING_ITEM_FLAG = 'Y'
WHERE INVENTORY_ITEM_ID IN ( 28939, 29657)
AND ORGANIZATION_ID = 121;
SELECT SEGMENT1
FROM MTL_SYSTEM_ITEMS_B
WHERE INVENTORY_ITEM_ID IN (SELECT ITEM_ID FROM XX_PO_INT_LLC)
AND (NVL(PURCHASING_ENABLED_FLAG,'N') <> 'Y' OR NVL(PURCHASING_ITEM_FLAG,'N') <> 'Y')
AND ORGANIZATION_ID = 122;
/*
delete from po_headers_interface;
delete from po_lines_interface;
delete from po_interface_errors;
SELECT * FROM
--delete from
po_headers_interface
WHERE PROCESS_CODE <> 'ACCEPTED'
;
SELECT * FROM
--delete from
po_lines_interface;
select * from
--delete from
po_interface_errors;
select *
from mtl_system_items_b
where inventory_item_id in (29657,28939)
and organization_id = 121
;
SELECT * FROM PO_HEADERS_ALL
WHERE SEGMENT1 = '23323';
select * from po_lines_all
where po_header_id = 7291;
*/
--1 MISSED LINES AND PO after upload no need to do anything, all PO lines will be rejected if there is one line issue
--just delete the interface tables for rejected lines and reupload again and run the conc program
--MISSED LINES
SELECT * FROM po_lines_interface
WHERE PROCESS_CODE = 'REJECTED'
;
SELECT * FROM PO_HEADERS_ALL WHERE PO_HEADER_ID = 3033;
SELECT document_num po_number,
--(SELECT SEGMENT1 FROM PO_HEADERS_ALL POH WHERE PO_HEADER_ID = XX1.PO_HEADER_ID) PO_NUMBER,
XX1.LINE_NUM, (SELECT SEGMENT1 FROM MTL_SYSTEM_ITEMS_B MTL WHERE INVENTORY_ITEM_ID = ITEM_ID AND ORGANIZATION_ID =121)ITEM_CODE,
UNIT_OF_MEASURE,
QUANTITY,
UNIT_PRICE
FROM po_lines_interface XX1 , po_headers_interface xx2
WHERE xx1.PROCESS_CODE = 'REJECTED'
and xx1.INTERFACE_HEADER_ID = xx2.INTERFACE_HEADER_ID
;
SELECT * FROM PO_HEADERS_ALL WHERE ORG_ID = 121;
UPDATE MTL_SYSTEM_ITEMS_B
SET PURCHASING_ENABLED_FLAG = 'Y',
PURCHASING_ITEM_FLAG = 'Y' WHERE
--SELECT SEGMENT1 FROM MTL_SYSTEM_ITEMS_B
--WHERE ORGANIZATION_ID = 121
INVENTORY_ITEM_ID IN(
SELECT ITEM_ID
FROM XX_PO_INT_TEST
WHERE VENDOR_ID IS NOT NULL AND VENDOR_SITE_ID IS NOT NULL
AND OLD_REF_NUM NOT IN (SELECT SEGMENT1 FROM PO_HEADERS_ALL WHERE ORG_ID = 121)
--AND CURRENCY <> 'AED'
AND OLD_REF_NUM <> '23945'
AND QUANTITY > 0
--ORDER BY OLD_REF_NUM
)
--TOTAL ROWS 1334
--ALREADY EXISTING PO WITH ROWS = 13
--NEW ROWS 1321
--CHECK UOM ALSO LATER
--RUN AED FIRST AND THEN OTHERS
;
SELECT *
FROM PO_HEADERS_ALL WHERE ORG_ID = 122;
------------------API----------------------
declare
L_ORG_ID NUMBER := 122; --1 121 from global, 123 for lifeco
L_AGENT_ID NUMBER := 599; --2 person_id of final_approver so the PO can be approved once uploaded LIFECO 781 , GLOBAL/LLC 599 CHK AGAIN
L_LOCATION_ID NUMBER := 202; --3 static for each Org eg: global 142, etc 144 for lifeco, 202 FOR LLC
L_CREATED_BY NUMBER := 1337; --1337 GLOBAL/LLC, 1232 LIFECO,
L_OLD_REF_NUM NUMBER := 1;
--RATE_TYPE, RATE_DATE ADD THIS FOR CURRENCY OTHER THAN 'AED'
L_po_headers_interface_ID NUMBER;
L_NEED_BY_DATE DATE;
cursor c1 is
SELECT
*
--DISTINCT OLD_REF_NUM
FROM
--EDIT
XX_PO_INT_TEST
WHERE VENDOR_ID IS NOT NULL AND VENDOR_SITE_ID IS NOT NULL
AND OLD_REF_NUM <> '5336'
AND OLD_REF_NUM NOT IN (SELECT SEGMENT1 FROM PO_HEADERS_ALL WHERE ORG_ID = 122)
AND CURRENCY = 'AED' --RATE_TYPE, RATE_DATE ADD THIS FOR CURRENCY OTHER THAN 'AED' UNDER HEADER LEVEL
AND NVL(QUANTITY,0) > 0
--AND OLD_REF_NUM = '2681'
ORDER BY OLD_REF_NUM,line_Num
;
BEGIN
FOR I IN C1 LOOP
L_NEED_BY_DATE := TO_DATE(I.PO_DATE,'DD-Mon-YYYY')+1;
IF L_OLD_REF_NUM = I.OLD_REF_NUM THEN
-- IF TO_DATE(I.PO_DATE,'MM/DD/YYYY') < TO_DATE(I.NEED_BY_DATE,'MM/DD/YYYY') THEN
-- L_NEED_BY_DATE := TO_DATE(I.NEED_BY_DATE,'MM/DD/YYYY');
-- ELSE
-- L_NEED_BY_DATE := TO_DATE(I.PO_DATE,'MM/DD/YYYY')+1;
-- END IF;
INSERT INTO po_lines_interface (interface_line_id,
interface_header_id,
line_type,
line_num,
--unit_of_measure,
UOM_CODE,
quantity,
unit_price,
item_id,
--item_description,
ship_to_location_id,
ship_to_organization_id,
creation_date,
created_by,
--shipment_num,
--category_id,
promised_date
--,LINE_ATTRIBUTE15 --for tech reference of SNO
)
VALUES (po_lines_interface_s.NEXTVAL, -- interface_line_id
po_headers_interface_s.CURRVAL,-- interface_header_id
--L_po_headers_interface_ID,
'Goods', -- line_type
i.line_num, -- line_num
--I.UNIT_OF_MEASURE, -- unit_of_measure
I.UOM_CODE,
I.QUANTITY, -- quantity
TRUNC(I.UNIT_PRICE,2), -- unit_price
I.ITEM_ID, -- item_id
--ITEM_DESC
L_LOCATION_ID, -- ship_to_location_id
L_ORG_ID, --ship_to_organization_id
TO_DATE(I.PO_DATE,'DD-Mon-YYYY'), -- creation_date
L_CREATED_BY, -- created_by
--1, -- shipment_num
--1, -- category_id
L_NEED_BY_DATE--promised_date
--,i.sno
);
ELSE
INSERT INTO po_headers_interface
(
document_num, --po_number
interface_header_id,
batch_id,
action,
document_type_code,
vendor_id,
vendor_site_id,
org_id,
currency_code,
agent_id,
ship_to_location_id,
bill_to_location_id,
approved_date,
creation_date,
created_by,
effective_date
--,RATE_TYPE
--,RATE_DATE
--,ATTRIBUTE3,ATTRIBUTE4
--,ATTRIBUTE5
--,REFERENCE_NUM
)
VALUES (I.OLD_REF_NUM, --po_number
po_headers_interface_s.NEXTVAL,--interface_header_id,
--L_po_headers_interface_ID,
1114, -- batch_id
'ORIGINAL', -- action
'STANDARD', -- document_type_code
I.VENDOR_ID, -- vendor_id
I.VENDOR_SITE_ID, -- vendor_site_id
L_ORG_ID, -- org_id
I.CURRENCY, -- currency_code
L_AGENT_ID, -- agent_id
L_LOCATION_ID, -- ship_to_location_id
L_LOCATION_ID, -- bill_to_location_id
SYSDATE, -- approved_date
TO_DATE(I.PO_DATE,'DD-Mon-YYYY'), -- creation_date
L_CREATED_BY, -- created_by
TO_DATE(I.PO_DATE,'DD-Mon-YYYY') --effective_date
--,'Corporate'
--,to_date('31-MAR-2023')
--,I.ATTRIBUTE3,I.ATTRIBUTE4
--,I.ATTRIBUTE5
--,I.REFERENCE_2
);
INSERT INTO po_lines_interface (interface_line_id,
interface_header_id,
line_type,
line_num,
--unit_of_measure,
UOM_CODE,
quantity,
unit_price,
item_id,
--item_description,
ship_to_location_id,
ship_to_organization_id,
creation_date,
created_by,
--shipment_num,
--category_id,
promised_date
--,LINE_ATTRIBUTE15
)
VALUES (po_lines_interface_s.NEXTVAL, -- interface_line_id
po_headers_interface_s.CURRVAL,-- interface_header_id
--L_po_headers_interface_ID,
'Goods', -- line_type
i.line_num, -- line_num
--I.UNIT_OF_MEASURE, -- unit_of_measure
I.UOM_CODE,
I.QUANTITY, -- quantity
TRUNC(I.UNIT_PRICE,2), -- unit_price
I.ITEM_ID, -- item_id
--ITEM_DESC
L_LOCATION_ID, -- ship_to_location_id
L_ORG_ID, --ship_to_organization_id
TO_DATE(I.PO_DATE,'DD-Mon-YYYY'), -- creation_date
L_CREATED_BY, -- created_by
--1, -- shipment_num
--1, -- category_id
L_NEED_BY_DATE--promised_date
--,i.sno
);
L_OLD_REF_NUM := I.OLD_REF_NUM;
END IF;
END LOOP;
END;
--run the conc program from the front end purchasing with any user as we uplaoded data with the approved user
--check 1 if tax is defaulted, ask to confirm with tax yes or no.
--Import Standard Purchase Order
--Create or Update Items - Yes
--Approval Status - APPROVED
--Batch -<select from lov>
--rest all keep default
--output Query
SELECT
SEGMENT1 PO_NUMBBER,
(SELECT VENDOR_NAME FROM AP_SUPPLIERS AP WHERE AP.VENDOR_ID = PO.VENDOR_ID)VENDOR_NAME,
--VENDOR_SITE_ID,
LINE_NUM,
(SELECT SEGMENT1 FROM MTL_SYSTEM_ITEMS_B WHERE INVENTORY_ITEM_ID = ITEM_ID AND ORGANIZATION_ID = 122)ITEM_CODE,
ITEM_DESCRIPTION,
UNIT_MEAS_LOOKUP_CODE,
UNIT_PRICE,
QUANTITY
--SELECT PO.*
FROM PO_LINES_ALL POL, PO_HEADERS_ALL PO
WHERE POL.LAST_UPDATE_DATE LIKE SYSDATE AND
PO.PO_HEADER_ID = POL.PO_HEADER_ID
AND PO.ORG_ID = 122
ORDER BY 1 ,3
;
