ORACLE APPS

Sunday, 16 March 2014

INTERFACE OUTBOUND

EXAMPLE:--
---------
CREATE OR REPLACE PACKAGE XX_EMP
IS
PROCEDURE XX_EMP1 (V_ERRBUF OUT VARCHAR2,V_RETCODE OUT VARCHAR2);
END XX_EMP;

CREATE OR REPLACE PACKAGE BODY XX_EMP
IS
PROCEDURE XX_EMP1(V_ERRBUF OUT VARCHAR2,V_RETCODE OUT VARCHAR2)
IS
CURSOR C1 IS SELECT ENAME,SAL,DEPTNO FROM EMP;
FP UTL_FILE.FILE_TYPE;
BEGIN
FP:=UTL_FILE.FOPEN('C:\TEMP','EMP.txt','W');---(OR).dat
UTL_FILE.PUT_LINE(FP,RPAD(INITCAP('ENAME'),10)
||' '||RPAD(INITCAP('SAL'),10)
||' '||RPAD(INITCAP('DEPTNO'),10));
fnd_file.put_line(fnd_file.output,RPAD(INITCAP('ENAME'),10)
||' '||RPAD(INITCAP('SAL'),10)
||' '||RPAD(INITCAP('DEPTNO'),10));
UTL_FILE.PUT_LINE(FP,RPAD('-',10,'-')
||' '||RPAD('-',10,'-')
||' '||RPAD('-',10,'-'));
fnd_file.put_line(fnd_file.output,RPAD('-',10,'-')
||' '||RPAD('-',10,'-')
||' '||RPAD('-',10,'-'));
FOR I IN C1
LOOP
UTL_FILE.PUT_LINE(FP,RPAD(I.ENAME,10)||' '||
RPAD(I.SAL,10)||' '||
RPAD(I.DEPTNO,10));
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,RPAD(I.ENAME,10)||' '||
RPAD(I.SAL,10)||' '||
RPAD(I.DEPTNO,10));
END LOOP;
UTL_FILE.FCLOSE(FP);
END XX_EMP1;
END XX_EMP;
==========================
1.InvoiceNumber,VendorID,VendorSiteId,OrgId,InvoiceAmount
CREATE OR REPLACE PACKAGE XX_AP_OUT_PKG
IS
PROCEDURE XX_MAIN(V_ERRBUF OUT VARCHAR2,V_RETCODE OUT VARCHAR2);
END XX_AP_OUT_PKG;

CREATE OR REPLACE PACKAGE BODY XX_AP_OUT_PKG
IS
PROCEDURE XX_MAIN(V_ERRBUF OUT VARCHAR2,V_RETCODE OUT VARCHAR2)
IS
CURSOR C1 IS
SELECT AIA.INVOICE_NUM
,AIA.VENDOR_ID
,AIA.VENDOR_SITE_ID
,AIA.ORG_ID
,AIA.INVOICE_AMOUNT
FROM AP_INVOICES_ALL AIA
WHERE ROWNUM<=100;
F_TYPE UTL_FILE.FILE_TYPE;
BEGIN
FND_FILE.PUT_LINE(FND_FILE.LOG,'ENTERED THE BEGIN THE MAIN BLOCK..');
F_TYPE:=UTL_FILE.FOPEN('C:\TEMP','XX_AP_INV.txt','W');--(OR).dat
UTL_FILE.PUT_LINE(F_TYPE, RPAD(INITCAP('INVOICE NUM'),20) ||' '||
RPAD(INITCAP('VENDOR_ID'),12) ||' '||
RPAD(INITCAP('VENDOR_SITE_ID'),15)||' '||
RPAD(INITCAP('ORG_ID'),7) ||' '||
LPAD(INITCAP('INVOICE_AMOUNT'),15));
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,RPAD(INITCAP('INVOICE NUM'),20) ||' '||
RPAD(INITCAP('VENDOR_ID'),20) ||' '||
RPAD(INITCAP('VENDOR_SITE_ID'),20)||' '||
RPAD(INITCAP('ORG_ID'),20) ||' '||
LPAD(INITCAP('INVOICE_AMOUNT'),20));
UTL_FILE.PUT_LINE(F_TYPE, TRIM(RPAD('-',20,'-'))||' '||
TRIM(RPAD('-',12,'-'))||' '||
TRIM(RPAD('-',18,'-'))||' '||
TRIM(RPAD('-',10,'-'))||' '||
TRIM(RPAD('-',19,'-')));
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,RPAD('-',20,'-')||' '||
RPAD('-',20,'-')||' '||
RPAD('-',20,'-')||' '||
RPAD('-',20,'-')||' '||
RPAD('-',20,'-'));
FOR I IN C1
LOOP
FND_FILE.PUT_LINE(FND_FILE.LOG,'ENTERED INTO LOOP..');
UTL_FILE.PUT_LINE(F_TYPE, LTRIM(RPAD(I.INVOICE_NUM,20))
||' '||LTRIM(RPAD(I.VENDOR_ID,12))
||' '||LTRIM(RPAD(I.VENDOR_SITE_ID,18))
||' '||LTRIM(RPAD(I.ORG_ID,10))
||' '||LTRIM(LPAD(I.INVOICE_AMOUNT ,19)));
FND_FILE.PUT_LINE(FND_FILE.OUTPUT, RPAD(I.INVOICE_NUM,20)
||' '||RPAD(I.VENDOR_ID,20)
||' '||RPAD(I.VENDOR_SITE_ID,20)
||' '||RPAD(I.ORG_ID,20)
||' '||LPAD(I.INVOICE_AMOUNT ,20));
END LOOP;
FND_FILE.PUT_LINE(FND_FILE.LOG,'AM AT OUT OF LOOP');
UTL_FILE.FCLOSE(F_TYPE);
END XX_MAIN;
END XX_AP_OUT_PKG;
-------------
DECLARE
V_ERRBUF VARCHAR2(100);
V_RETCODE VARCHAR2(100);
BEGIN
XX_AP_OUT_PKG.XX_MAIN(V_ERRBUF,V_RETCODE);
END;
===============================
2.reqnum,reqtype operating unit preapare reqamount linenum,item,qty,price,totalprice
--------- -------- ---------- --------------- ------------
parameters :Operating Unit(name)
from req(optional)
to req(optional)
-------------------------------
CREATE OR REPLACE PACKAGE XX_REQ_OUT_PKG
IS
PROCEDURE XX_REQ_PROC(V_ERRBUF OUT VARCHAR2,V_RETCODE VARCHAR2 ,P_OPERATINGUNIT VARCHAR2,P_FROM_REQ_NUM VARCHAR2,P_TO_REQ_NUM VARCHAR2);
END XX_REQ_OUT_PKG;
-----------------------------
CREATE OR REPLACE PACKAGE BODY XX_REQ_OUT_PKG
IS
PROCEDURE XX_REQ_PROC(V_ERRBUF OUT VARCHAR2,V_RETCODE VARCHAR2 ,P_OPERATINGUNIT VARCHAR2,P_FROM_REQ_NUM VARCHAR2,P_TO_REQ_NUM VARCHAR2)
IS
CURSOR C1 IS
SELECT PRHA.SEGMENT1 REQNUM
,PDTA.TYPE_NAME REQTYPE
,HOU.NAME OPERATINGUNIT
,PAPF.FULL_NAME PREPAR
,PRLA.LINE_NUM
,MSIB.SEGMENT1 ITEM
,PRLA.QUANTITY
,PRLA.UNIT_PRICE PRICE
,PRLA.QUANTITY*PRLA.UNIT_PRICE TOTALPRICE
FROM PO_REQUISITION_HEADERS_ALL PRHA
,PO_REQUISITION_LINES_ALL PRLA
,PO_DOCUMENT_TYPES_ALL PDTA
,HR_OPERATING_UNITS HOU
,PER_ALL_PEOPLE_F PAPF
,MTL_SYSTEM_ITEMS_B MSIB
,ORG_ORGANIZATION_DEFINITIONS OOD
WHERE HOU.NAME=P_OPERATINGUNIT
AND PRHA.SEGMENT1 BETWEEN NVL(P_FROM_REQ_NUM,PRHA.SEGMENT1) AND NVL(P_TO_REQ_NUM,PRHA.SEGMENT1)
AND PRHA.REQUISITION_HEADER_ID=PRLA.REQUISITION_HEADER_ID
AND PRHA.TYPE_LOOKUP_CODE=PDTA.DOCUMENT_SUBTYPE
AND PRHA.ORG_ID=PDTA.ORG_ID
AND PRHA.ORG_ID=HOU.ORGANIZATION_ID
AND PRHA.PREPARER_ID=PAPF.PERSON_ID
AND PRLA.ITEM_ID=MSIB.INVENTORY_ITEM_ID
AND PRHA.ORG_ID=MSIB.ORGANIZATION_ID
AND MSIB.ORGANIZATION_ID=OOD.ORGANIZATION_ID
AND ROWNUM<=20 ;
F_TYPE UTL_FILE.FILE_TYPE;
BEGIN
FND_FILE.PUT_LINE(FND_FILE.LOG,'ENTERED MAIN BLOCK..');
F_TYPE:=UTL_FILE.FOPEN('C:\TEMP','XX_REQ_DETAILS.txt','W');--(OR).dat
UTL_FILE.PUT_LINE(F_TYPE, RPAD(INITCAP('REQNUM'),10)
||' '||RPAD(INITCAP('REQTYPE'),23)
||' '||RPAD(INITCAP('OPERATINGUNIT'),20)
||' '||RPAD(INITCAP('PREPAR'),17)
||' '||RPAD(INITCAP('LINE_NUM'),5)
||' '||RPAD(INITCAP('ITEM'),10)
||' '||RPAD(INITCAP('QUANTITY'),10)
||' '||RPAD(INITCAP('PRICE'),10)
||' '||RPAD(INITCAP('TOTALPRICE'),10));
FND_FILE.PUT_LINE(FND_FILE.OUTPUT, RPAD(INITCAP('REQNUM'),10)
||' '||RPAD(INITCAP('REQTYPE'),25)
||' '||RPAD(INITCAP('OPERATINGUNIT'),20)
||' '||RPAD(INITCAP('PREPAR'),15)
||' '||RPAD(INITCAP('LINE_NUM'),5)
||' '||RPAD(INITCAP('ITEM'),10)
||' '||RPAD(INITCAP('QUANTITY'),10)
||' '||RPAD(INITCAP('PRICE'),10)
||' '||RPAD(INITCAP('TOTALPRICE'),10));
UTL_FILE.PUT_LINE(F_TYPE, RPAD('-',10,'-')
||' '||RPAD('-',23,'-')
||' '||RPAD('-',20,'-')
||' '||RPAD('-',17,'-')
||' '||RPAD('-',5,'-')
||' '||RPAD('-',10,'-')
||' '||RPAD('-',10,'-')
||' '||RPAD('-',10,'-')
||' '||RPAD('-',10,'-'));
FND_FILE.PUT_LINE(FND_FILE.OUTPUT, RPAD('-',10,'-')
||' '||RPAD('-',25,'-')
||' '||RPAD('-',20,'-')
||' '||RPAD('-',15,'-')
||' '||RPAD('-',5,'-')
||' '||RPAD('-',10,'-')
||' '||RPAD('-',10,'-')
||' '||RPAD('-',10,'-')
||' '||RPAD('-',10,'-'));
FOR I IN C1
LOOP
FND_FILE.PUT_LINE(FND_FILE.LOG,'ENTERED LOOP..');
UTL_FILE.PUT_LINE( F_TYPE, RPAD(I.REQNUM,10)
||' '||RPAD(I.REQTYPE,23)
||' '||RPAD(I.OPERATINGUNIT,25)
||' '||RPAD(I.PREPAR,10)
||' '||LPAD(I.LINE_NUM,5)
||' '||RPAD(I.ITEM,10)
||' '||LPAD(I.QUANTITY,10)
||' '||LPAD(I.PRICE,10)
||' '||LPAD(I.TOTALPRICE,10));
FND_FILE.PUT_LINE(FND_FILE.OUTPUT, RPAD(I.REQNUM,10)
||' '||RPAD(I.REQTYPE,25)
||' '||RPAD(I.OPERATINGUNIT,20)
||' '||RPAD(I.PREPAR,15)
||' '||LPAD(I.LINE_NUM,5)
||' '||RPAD(I.ITEM,10)
||' '||LPAD(I.QUANTITY,10)
||' '||LPAD(I.PRICE,10)
||' '||LPAD(I.TOTALPRICE,10));
END LOOP;
FND_FILE.PUT_LINE(FND_FILE.LOG,'AT END OF THE LOOP..');
UTL_FILE.FCLOSE(F_TYPE);
END XX_REQ_PROC;
END XX_REQ_OUT_PKG; --XX_REQ_OUT_PKG.XX_REQ_PROC
=====================================================================
3.create a file with the fllowing datails
ponumber,potype,suplier,suppliersite,shipto,bilto,poamount,linenum,linetyp,item,qty,price Vision Operations
----------- ------- ------- ------------- ------- 3M Health Care
input paramaters:-->Operating Unit , Supplier Name
--------------------------------------------
CREATE OR REPLACE PACKAGE XX_PO_OUT_PKG
IS
PROCEDURE XX_PO_PROC (V_ERRBUF OUT VARCHAR2,V_RETCODE OUT VARCHAR2,P_OPERATINGUNIT VARCHAR2,P_SUPPLIER VARCHAR2);
END XX_PO_OUT_PKG;
------------------
CREATE OR REPLACE PACKAGE BODY XX_PO_OUT_PKG
IS
PROCEDURE XX_PO_PROC (V_ERRBUF OUT VARCHAR2,V_RETCODE OUT VARCHAR2,P_OPERATINGUNIT VARCHAR2,P_SUPPLIER VARCHAR2)
IS
CURSOR C1
IS
SELECT PHA.SEGMENT1 PONUMBER
,PDTA.TYPE_NAME POTYPE
,APS.VENDOR_NAME SUPPLIER
,APSA.VENDOR_SITE_CODE SUPPLIERSITE
,HL.LOCATION_CODE SHIPTO
,HL1.LOCATION_CODE BILTO
,PLA.LINE_NUM
,PLTL.LINE_TYPE
,MSIB.SEGMENT1 ITEM
,PLA.QUANTITY
,PLA.UNIT_PRICE PRICE
,PLA.UNIT_PRICE*PLA.QUANTITY AMOUNT
FROM PO_HEADERS_ALL PHA
,PO_DOCUMENT_TYPES_ALL PDTA
,AP_SUPPLIERS APS
,AP_SUPPLIER_SITES_ALL APSA
,HR_LOCATIONS HL
,HR_LOCATIONS HL1
,PO_LINES_ALL PLA
,PO_LINE_TYPES_TL PLTL
,MTL_SYSTEM_ITEMS_B MSIB
,ORG_ORGANIZATION_DEFINITIONS OOD
,HR_OPERATING_UNITS HOU
WHERE HOU.NAME=P_OPERATINGUNIT --'Vision Operations' --
AND APS.VENDOR_NAME=P_SUPPLIER --'3M Health Care' --
AND PHA.TYPE_LOOKUP_CODE=PDTA.DOCUMENT_SUBTYPE
AND PHA.ORG_ID=PDTA.ORG_ID
AND PHA.VENDOR_ID=APS.VENDOR_ID
AND PHA.VENDOR_SITE_ID=APSA.VENDOR_SITE_ID
AND APS.VENDOR_ID=APSA.VENDOR_ID
AND HL.LOCATION_ID=PHA.SHIP_TO_LOCATION_ID
AND HL1.LOCATION_ID=PHA.BILL_TO_LOCATION_ID
AND PHA.PO_HEADER_ID=PLA.PO_HEADER_ID
AND PLA.LINE_TYPE_ID=PLTL.LINE_TYPE_ID
AND PLA.ITEM_ID=MSIB.INVENTORY_ITEM_ID
AND PHA.ORG_ID=MSIB.ORGANIZATION_ID
AND MSIB.ORGANIZATION_ID=OOD.ORGANIZATION_ID
AND PHA.ORG_ID=HOU.ORGANIZATION_ID
AND ROWNUM<=30;
F_TYPE UTL_FILE.FILE_TYPE;
BEGIN
FND_FILE.PUT_LINE(FND_FILE.LOG,'ENTERE IN MAIN BLOCK.');
F_TYPE:=UTL_FILE.FOPEN('C:\TEMP','XX_PO_DETAILS.txt','W');--(OR).dat
UTL_FILE.PUT_LINE(F_TYPE,RPAD(INITCAP('PONUMBER'),10)
||' '||RPAD(INITCAP('POTYPE'),15)
||' '||RPAD(INITCAP('SUPPLIER'),15)
||' '||RPAD(INITCAP('SUPPLIERSITE'),15)
||' '||RPAD(INITCAP('SHIPTO'),20)
||' '||RPAD(INITCAP('BILTO'),20)
||' '||RPAD(INITCAP('LINE_NUM'),7)
||' '||RPAD(INITCAP('LINE_TYPE'),10)
||' '||RPAD(INITCAP('ITEM'),10)
||' '||RPAD(INITCAP('QUANTITY'),10)
||' '||RPAD(INITCAP('PRICE'),10)
||' '||RPAD(INITCAP('AMOUNT'),10));
FND_FILE.PUT_LINE(FND_FILE.OUTPUT, RPAD(INITCAP('PONUMBER'),10)
||' '||RPAD(INITCAP('POTYPE'),15)
||' '||RPAD(INITCAP('SUPPLIER'),15)
||' '||RPAD(INITCAP('SUPPLIERSITE'),15)
||' '||RPAD(INITCAP('SHIPTO'),20)
||' '||RPAD(INITCAP('BILTO'),20)
||' '||RPAD(INITCAP('LINE_NUM'),7)
||' '||RPAD(INITCAP('LINE_TYPE'),10)
||' '||RPAD(INITCAP('ITEM'),10)
||' '||RPAD(INITCAP('QUANTITY'),10)
||' '||RPAD(INITCAP('PRICE'),10)
||' '||RPAD(INITCAP('AMOUNT'),10));
UTL_FILE.PUT_LINE(F_TYPE,RPAD('-',10,'-')
||' '||RPAD('-',15,'-')
||' '||RPAD('-',15,'-')
||' '||RPAD('-',15,'-')
||' '||RPAD('-',20,'-')
||' '||RPAD('-',20,'-')
||' '||RPAD('-',7,'-')
||' '||RPAD('-',10,'-')
||' '||RPAD('-',10,'-')
||' '||RPAD('-',10,'-')
||' '||RPAD('-',10,'-')
||' '||RPAD('-',10,'-'));
FND_FILE.PUT_LINE(FND_FILE.OUTPUT, RPAD('-',10,'-')
||' '||RPAD('-',15,'-')
||' '||RPAD('-',15,'-')
||' '||RPAD('-',15,'-')
||' '||RPAD('-',20,'-')
||' '||RPAD('-',20,'-')
||' '||RPAD('-',7,'-')
||' '||RPAD('-',10,'-')
||' '||RPAD('-',10,'-')
||' '||RPAD('-',10,'-')
||' '||RPAD('-',10,'-')
||' '||RPAD('-',10,'-'));
FOR I IN C1
LOOP
FND_FILE.PUT_LINE(FND_FILE.LOG,'ENTERED LOOP..');
UTL_FILE.PUT_LINE(F_TYPE, RPAD(I.PONUMBER,10)
||' '||RPAD(I.POTYPE,15)
||' '||RPAD(I.SUPPLIER,15)
||' '||RPAD(I.SUPPLIERSITE,15)
||' '||RPAD(I.SHIPTO,20)
||' '||RPAD(I.BILTO,20)
||' '||LPAD(I.LINE_NUM,7)
||' '||RPAD(I.LINE_TYPE,10)
||' '||RPAD(I.ITEM,10)
||' '||LPAD(I.QUANTITY,10)
||' '||LPAD(I.PRICE,10)
||' '||LPAD(I.AMOUNT,10));
FND_FILE.PUT_LINE(FND_FILE.OUTPUT, RPAD(I.PONUMBER,10)
||' '||RPAD(I.POTYPE,15)
||' '||RPAD(I.SUPPLIER,15)
||' '||RPAD(I.SUPPLIERSITE,15)
||' '||RPAD(I.SHIPTO,20)
||' '||RPAD(I.BILTO,20)
||' '||LPAD(I.LINE_NUM,7)
||' '||RPAD(I.LINE_TYPE,10)
||' '||RPAD(I.ITEM,10)
||' '||LPAD(I.QUANTITY,10)
||' '||LPAD(I.PRICE,10)
||' '||LPAD(I.AMOUNT,10));
END LOOP;
FND_FILE.PUT_LINE(FND_FILE.LOG,'AT END OF THE LOOP..');
UTL_FILE.FCLOSE(F_TYPE);
END XX_PO_PROC;
END XX_PO_OUT_PKG;
==============================================
4.PROCEDURE --> create a file with the following details
Operating Unit Order Number Order Type Price List Customer Cust Account Ship To Bill To Amount
---------------- --------------- ------------ ---------- ---------- -------------- -------- ------- --------
Line Num Item Qty Price Total Price Terms Name Ship To Bill To
---------- ----- ---- ----- ----------- ------------- -------- -------
Parameters :
--------------
Operating Unit
From Customer
To Customer
From Order Number ----> Optional
To Order Number ----> Optional
Booked Only -- Yes
No
----------------------------------------------------------------
CREATE OR REPLACE PACKAGE XX_SALES_PKG
IS
PROCEDURE XX_SALES_PROC(V_ERRBUF OUT VARCHAR2
,V_RETCODE OUT VARCHAR2
,P_OPERATINGUNIT VARCHAR2
,P_FROM_CUSTOMER VARCHAR2
,P_TO_CUSTOMER VARCHAR2
,P_FROM_ORDER VARCHAR2
,P_TO_ORDER VARCHAR2
,P_ORDER_STATES VARCHAR2);
END XX_SALES_PKG;

CREATE OR REPLACE PACKAGE BODY XX_SALES_PKG
IS
PROCEDURE XX_SALES_PROC(V_ERRBUF OUT VARCHAR2
,V_RETCODE OUT VARCHAR2
,P_OPERATINGUNIT VARCHAR2
,P_FROM_CUSTOMER VARCHAR2
,P_TO_CUSTOMER VARCHAR2
,P_FROM_ORDER VARCHAR2
,P_TO_ORDER VARCHAR2
,P_ORDER_STATES VARCHAR2)
IS
CURSOR C1 IS
SELECT HOU.NAME OPERATINGUNIT
,OOHA.ORDER_NUMBER
,OTTT.NAME ORDERTYPE
,QLH.NAME PRICELIST
,HP.PARTY_NAME CUSTOMER
,HCA.ACCOUNT_NUMBER
,HCSUA.LOCATION ||','||(HL.ADDRESS1
||','||HL.CITY
||','||HL.STATE
||','||HL.POSTAL_CODE
||','||HL.COUNTY) SHIPTO
,HCSUA1.LOCATION ||','||(HL1.ADDRESS1
||','||HL1.CITY
||','||HL1.STATE
||','||HL1.POSTAL_CODE
||','||HL1.COUNTY)BILTO
FROM HR_OPERATING_UNITS HOU
,OE_ORDER_HEADERS_ALL OOHA
,OE_TRANSACTION_TYPES_TL OTTT
,QP_LIST_HEADERS QLH
,HZ_PARTIES HP
,HZ_CUST_ACCOUNTS HCA
,HZ_CUST_SITE_USES_ALL HCSUA
,HZ_CUST_SITE_USES_ALL HCSUA1
,HZ_LOCATIONS HL
,HZ_LOCATIONS HL1
,HZ_PARTY_SITES HPS
,HZ_CUST_ACCT_SITES_ALL HCASA
WHERE HOU.NAME=P_OPERATINGUNIT
AND HP.PARTY_NAME BETWEEN P_FROM_CUSTOMER AND P_TO_CUSTOMER
AND OOHA.ORDER_NUMBER BETWEEN NVL(P_FROM_ORDER,OOHA.ORDER_NUMBER)AND NVL(P_FROM_ORDER,OOHA.ORDER_NUMBER)
AND OOHA.FLOW_STATUS_CODE =(CASE WHEN P_ORDER_STATES='YES' THEN 'BOOKED'
WHEN P_ORDER_STATES='NO' THEN OOHA.FLOW_STATUS_CODE
END)
AND OOHA.ORG_ID=HOU.ORGANIZATION_ID
AND OOHA.ORDER_TYPE_ID=OTTT.TRANSACTION_TYPE_ID
AND OOHA.PRICE_LIST_ID=QLH.LIST_HEADER_ID
AND HP.PARTY_ID=HCA.PARTY_ID
AND HCA.CUST_ACCOUNT_ID=OOHA.SOLD_TO_ORG_ID
AND HCSUA.SITE_USE_ID=OOHA.SHIP_TO_ORG_ID
AND HCSUA1.SITE_USE_ID=OOHA.INVOICE_TO_ORG_ID
AND HL.LOCATION_ID=HPS.LOCATION_ID
AND HL1.LOCATION_ID=HPS.LOCATION_ID
AND HPS.PARTY_SITE_ID=HCASA.PARTY_SITE_ID
AND HCSUA.CUST_ACCT_SITE_ID=HCASA.CUST_ACCT_SITE_ID
AND ROWNUM<=10;
CURSOR C2 (P_ORDER_NUM VARCHAR2)IS
SELECT OOLA. LINE_NUMBER
,MSIB.SEGMENT1 ITEM
,OOLA. ORDERED_QUANTITY
,OOLA.UNIT_LIST_PRICE PRICE
,OOLA.ORDERED_QUANTITY*OOLA.UNIT_LIST_PRICE TOTALPRICE
,RTT.NAME TERMSNAME
,HCSUA.LOCATION SHIPTO
,HCSUA1.LOCATION BILTO
FROM OE_ORDER_HEADERS_ALL OOHA
,OE_ORDER_LINES_ALL OOLA
,MTL_SYSTEM_ITEMS_B MSIB
,ORG_ORGANIZATION_DEFINITIONS OOD
,HZ_CUST_SITE_USES_ALL HCSUA
,HZ_CUST_SITE_USES_ALL HCSUA1
,RA_TERMS_TL RTT
WHERE OOHA.ORDER_NUMBER=P_ORDER_NUM -- OOLA.LINE_ID=114711 --- OOHA.ORDER_NUMBER=66403 --
AND OOHA.HEADER_ID=OOLA.HEADER_ID
AND OOLA.INVENTORY_ITEM_ID=MSIB.INVENTORY_ITEM_ID
AND OOLA.ORG_ID=MSIB.ORGANIZATION_ID
AND MSIB.ORGANIZATION_ID=OOD.ORGANIZATION_ID
AND HCSUA.SITE_USE_ID=OOLA.SHIP_TO_ORG_ID
AND HCSUA1.SITE_USE_ID=OOLA.INVOICE_TO_ORG_ID
AND RTT.TERM_ID=OOLA.PAYMENT_TERM_ID ;
F_TYPE UTL_FILE.FILE_TYPE;
BEGIN
FND_FILE.PUT_LINE(FND_FILE.LOG,'Entered The Main Block..');
F_TYPE:=UTL_FILE.FOPEN('C:\TEMP','Sales Details.txt','W');
FOR I IN C1
LOOP
FND_FILE.PUT_LINE(FND_FILE.LOG,'Entered Into The First Loop..');
UTL_FILE.PUT_LINE(F_TYPE, RPAD(INITCAP('OPERATINGUNIT'),20)
||' '||RPAD(INITCAP('ORDER_NUMBER'),10)
||' '||RPAD(INITCAP('ORDERTYPE'),20)
||' '||RPAD(INITCAP('PRICELIST'),20)
||' '||RPAD(INITCAP('CUSTOMER'),10)
||' '||RPAD(INITCAP('ACCOUNT_NUMBER'),10)
||' '||RPAD(INITCAP('SHIPTO'),20)
||' '||RPAD(INITCAP('BILTO'),20));
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,RPAD(INITCAP('OPERATINGUNIT'),20)
||' '||RPAD(INITCAP('ORDER_NUMBER'),10)
||' '||RPAD(INITCAP('ORDERTYPE'),20)
||' '||RPAD(INITCAP('PRICELIST'),20)
||' '||RPAD(INITCAP('CUSTOMER'),10)
||' '||RPAD(INITCAP('ACCOUNT_NUMBER'),10)
||' '||RPAD(INITCAP('SHIPTO'),20)
||' '||RPAD(INITCAP('BILTO'),20));
UTL_FILE.PUT_LINE(F_TYPE, RPAD('-',20,'-')
||' '||RPAD('-',10,'-')
||' '||RPAD('-',20,'-')
||' '||RPAD('-',20,'-')
||' '||RPAD('-',10,'-')
||' '||RPAD('-',10,'-')
||' '||RPAD('-',20,'-')
||' '||RPAD('-',20,'-'));
FND_FILE.PUT_LINE(FND_FILE.OUTPUT, RPAD('-',20,'-')
||' '||RPAD('-',10,'-')
||' '||RPAD('-',20,'-')
||' '||RPAD('-',20,'-')
||' '||RPAD('-',10,'-')
||' '||RPAD('-',10,'-')
||' '||RPAD('-',20,'-')
||' '||RPAD('-',20,'-'));
UTL_FILE.PUT_LINE(F_TYPE, RPAD( I.OPERATINGUNIT,20)
||' '||RPAD(I.ORDER_NUMBER,10)
||' '||RPAD(I.ORDERTYPE,20)
||' '||RPAD(I.PRICELIST,20)
||' '||RPAD(I.CUSTOMER,10)
||' '||RPAD(I.ACCOUNT_NUMBER,10)
||' '||RPAD(I.SHIPTO,20)
||' '||RPAD(I.BILTO,20));
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,RPAD( I.OPERATINGUNIT,20)
||' '||RPAD(I.ORDER_NUMBER,10)
||' '||RPAD(I.ORDERTYPE,20)
||' '||RPAD(I.PRICELIST,20)
||' '||RPAD(I.CUSTOMER,10)
||' '||RPAD(I.ACCOUNT_NUMBER,10)
||' '||RPAD(I.SHIPTO,20)
||' '||RPAD(I.BILTO,20));
UTL_FILE.PUT_LINE(F_TYPE, RPAD(INITCAP('LINE_NUMBER'),10)
||' '||RPAD(INITCAP('ITEM'),10)
||' '||RPAD(INITCAP('ORDERED_QUANTITY'),10)
||' '||RPAD(INITCAP('PRICE'),10)
||' '||RPAD(INITCAP('TOTALPRICE'),10)
||' '||RPAD(INITCAP('TERMSNAME'),15)
||' '||RPAD(INITCAP('SHIPTO'),20)
||' '||RPAD(INITCAP(' BILTO'),20));
FND_FILE.PUT_LINE(FND_FILE.OUTPUT, RPAD(INITCAP('LINE_NUMBER'),10)
||' '||RPAD(INITCAP('ITEM'),10)
||' '||RPAD(INITCAP('ORDERED_QUANTITY'),10)
||' '||RPAD(INITCAP('PRICE'),10)
||' '||RPAD(INITCAP('TOTALPRICE'),10)
||' '||RPAD(INITCAP('TERMSNAME'),15)
||' '||RPAD(INITCAP('SHIPTO'),20)
||' '||RPAD(INITCAP(' BILTO'),20));
UTL_FILE.PUT_LINE(F_TYPE, RPAD('-',10,'-')
||' '||RPAD('-',10,'-')
||' '||RPAD('-',10,'-')
||' '||RPAD('-',10,'-')
||' '||RPAD('-',10,'-')
||' '||RPAD('-',15,'-')
||' '||RPAD('-',20,'-')
||' '||RPAD('-',20,'-'));
FND_FILE.PUT_LINE(FND_FILE.OUTPUT, RPAD('-',10,'-')
||' '||RPAD('-',10,'-')
||' '||RPAD('-',10,'-')
||' '||RPAD('-',10,'-')
||' '||RPAD('-',10,'-')
||' '||RPAD('-',15,'-')
||' '||RPAD('-',20,'-')
||' '||RPAD('-',20,'-'));
FOR J IN C2(I.ORDER_NUMBER)
LOOP
UTL_FILE.PUT_LINE(F_TYPE, RPAD(J. LINE_NUMBER,10)
||' '||RPAD(J.ITEM,10)
||' '||LPAD(J.ORDERED_QUANTITY,10)
||' '||LPAD(J.PRICE,10)
||' '||LPAD(J.TOTALPRICE ,10)
||' '||RPAD(J.TERMSNAME,15)
||' '||RPAD(J.SHIPTO,20)
||' '||RPAD(J.BILTO,20));
FND_FILE.PUT_LINE(FND_FILE.OUTPUT, RPAD(J. LINE_NUMBER,10)
||' '||RPAD(J.ITEM,10)
||' '||LPAD(J.ORDERED_QUANTITY,10)
||' '||LPAD(J.PRICE,10)
||' '||LPAD(J.TOTALPRICE ,10)
||' '||RPAD(J.TERMSNAME,15)
||' '||RPAD(J.SHIPTO,20)
||' '||RPAD(J.BILTO,20));
END LOOP;
END LOOP;
FND_FILE.PUT_LINE(FND_FILE.LOG,'AM AT END OF LOOP..');
UTL_FILE.FCLOSE(F_TYPE);
END XX_SALES_PROC;
END XX_SALES_PKG;
-----------
EXEC XX_PROD('Vision Operations','A. C. Networks','World of Business',66397,66397,'YES');
==============================================================================================
5. AP Invoice Payments outbound Interface
------------------------------------------------

--> create a file with the following details

Operating Unit, Invoice Number, Invoice Amount, Invoice Date, Supplier, Supplier Site, Check Number,
Payment Method, Payment Amount, Bank Name, Branch Name, Bank Account, Payment Date

Parameters :
--------------
Operating Unit
Supplier
Supplier Site ----> Optional
From Invoice Number ----> Optional
To Invoice Number ----> Optional
--------------------------------
CREATE OR REPLACE PACKAGE XX_AP_OUT_PKG
IS
PROCEDURE XX_AP_PROC(V_ERRBUF OUT VARCHAR2
,V_RETCODE OUT VARCHAR2
,P_OPERATINGUNIT VARCHAR2
,P_SUPPLIER VARCHAR2
,P_SUPPLIER_SITE VARCHAR2
,P_FROM_INV_NUM VARCHAR2
,P_TO_INV_NUM VARCHAR2);

END XX_AP_OUT_PKG;

CREATE OR REPLACE PACKAGE BODY XX_AP_OUT_PKG
IS
PROCEDURE XX_AP_PROC(V_ERRBUF OUT VARCHAR2
,V_RETCODE OUT VARCHAR2
,P_OPERATINGUNIT VARCHAR2
,P_SUPPLIER VARCHAR2
,P_SUPPLIER_SITE VARCHAR2
,P_FROM_INV_NUM VARCHAR2
,P_TO_INV_NUM VARCHAR2 )
IS
CURSOR C1 IS
SELECT HOU.NAME OperatingUnit
,AIA. INVOICE_NUM
,AIA. INVOICE_AMOUNT
,AILA.LINE_NUMBER
,AIA. INVOICE_DATE
,APS.VENDOR_NAME Supplier
,APSSA.VENDOR_SITE_CODE SupplierSite
,ACA.CHECK_NUMBER CheckNumber
,AIA.PAYMENT_METHOD_CODE PaymentMethod
,CBBV.BANK_BRANCH_NAME_ALT BranchName
,AIPA.AMOUNT PaymentAmount
,ACA.BANK_ACCOUNT_NAME BankName
,ACA.BANK_ACCOUNT_NUM BankAccount
,APSA.DUE_DATE PaymentDate
FROM HR_OPERATING_UNITS HOU
,AP_INVOICES_ALL AIA
,AP_INVOICE_LINES_ALL AILA
,AP_INVOICE_DISTRIBUTIONS_ALL AIDA
,AP_SUPPLIERS APS
,AP_SUPPLIER_SITES_ALL APSSA
,AP_INVOICE_PAYMENTS_ALL AIPA
,AP_PAYMENT_SCHEDULES_ALL APSA
,AP_CHECKS_ALL ACA
,CE_BANK_ACCOUNTS CBA
,CE_BANK_BRANCHES_V CBBV
WHERE HOU.NAME=P_OPERATINGUNIT
AND APS.VENDOR_NAME=P_SUPPLIER
AND APSSA.VENDOR_SITE_CODE=NVL(P_SUPPLIER_SITE,APSSA.VENDOR_SITE_CODE)
AND AIA.INVOICE_NUM BETWEEN NVL(P_FROM_INV_NUM,AIA.INVOICE_NUM)AND NVL(P_TO_INV_NUM,AIA.INVOICE_NUM) --AIA.INVOICE_NUM='34342'-LIKE '3%'-'INV55'- HOU.NAME='Vision Operations' -'INV 13A'
AND HOU.ORGANIZATION_ID=AIA.ORG_ID
AND AIA.INVOICE_ID=AILA.INVOICE_ID
AND AILA.LINE_NUMBER=AIDA.INVOICE_LINE_NUMBER
AND AIA.INVOICE_ID=AIDA.INVOICE_ID
AND AIA.VENDOR_ID=APS.VENDOR_ID
AND AIA.VENDOR_SITE_ID=APSSA.VENDOR_SITE_ID
AND AIA.VENDOR_ID=APSSA.VENDOR_ID
AND AIA.INVOICE_ID=AIPA.INVOICE_ID
AND AIA.INVOICE_ID=APSA.INVOICE_ID
AND AIPA.CHECK_ID=ACA.CHECK_ID
AND ACA.BANK_ACCOUNT_ID=CBA.BANK_ACCOUNT_ID
--AND CBA.BANK_BRANCH_ID=CBBV.BRANCH_PARTY_ID
AND CBA.BANK_ID=CBBV.BANK_PARTY_ID
AND ROWNUM<=100;
F_TYPE UTL_FILE.FILE_TYPE;
BEGIN
FND_FILE.PUT_LINE(FND_FILE.LOG,'Entered The Main Block..');
F_TYPE:=UTL_FILE.FOPEN('C:\TEMP','XX_AP_DETAILS.txt','W');
UTL_FILE.PUT_LINE(F_TYPE,RPAD(INITCAP('OperatingUnit' ),20)
||' '||RPAD(INITCAP('INVOICE_NUM' ),20)
||' '||RPAD(INITCAP('INVOICE_AMOUNT'),20)
||' '||RPAD(INITCAP('LINE_NUMBER' ),20)
||' '||RPAD(INITCAP('Supplier' ),20)
||' '||RPAD(INITCAP('SupplierSite' ),20)
||' '||RPAD(INITCAP('CheckNumber' ),20)
||' '||RPAD(INITCAP('PaymentMethod' ),20)
||' '||RPAD(INITCAP('BranchName' ),20)
||' '||RPAD(INITCAP('PaymentAmount' ),20)
||' '||RPAD(INITCAP('BankName' ),20)
||' '||RPAD(INITCAP('BankAccount' ),20)
||' '||RPAD(INITCAP('PaymentDate' ),20));
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,RPAD(INITCAP('OperatingUnit' ),20)
||' '||RPAD(INITCAP('INVOICE_NUM' ),20)
||' '||RPAD(INITCAP('INVOICE_AMOUNT'),20)
||' '||RPAD(INITCAP('LINE_NUMBER' ),20)
||' '||RPAD(INITCAP('Supplier' ),20)
||' '||RPAD(INITCAP('SupplierSite' ),20)
||' '||RPAD(INITCAP('CheckNumber' ),20)
||' '||RPAD(INITCAP('PaymentMethod' ),20)
||' '||RPAD(INITCAP('BranchName' ),20)
||' '||RPAD(INITCAP('PaymentAmount' ),20)
||' '||RPAD(INITCAP('BankName' ),20)
||' '||RPAD(INITCAP('BankAccount' ),20)
||' '||RPAD(INITCAP('PaymentDate' ),20));
UTL_FILE.PUT_LINE(F_TYPE, RPAD('-',20,'-')
||' '||RPAD('-',20,'-')
||' '||RPAD('-',20,'-')
||' '||RPAD('-',20,'-')
||' '||RPAD('-',20,'-')
||' '||RPAD('-',20,'-')
||' '||RPAD('-',20,'-')
||' '||RPAD('-',20,'-')
||' '||RPAD('-',20,'-')
||' '||RPAD('-',20,'-')
||' '||RPAD('-',20,'-')
||' '||RPAD('-',20,'-')
||' '||RPAD('-',20,'-'));
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,RPAD('-',20,'-')
||' '||RPAD('-',20,'-')
||' '||RPAD('-',20,'-')
||' '||RPAD('-',20,'-')
||' '||RPAD('-',20,'-')
||' '||RPAD('-',20,'-')
||' '||RPAD('-',20,'-')
||' '||RPAD('-',20,'-')
||' '||RPAD('-',20,'-')
||' '||RPAD('-',20,'-')
||' '||RPAD('-',20,'-')
||' '||RPAD('-',20,'-')
||' '||RPAD('-',20,'-'));
FOR I IN C1
LOOP
FND_FILE.PUT_LINE(FND_FILE.LOG,'Entered The Loop..');
UTL_FILE.PUT_LINE(F_TYPE,RPAD(I.OperatingUnit,20)
||' '||RPAD(I.INVOICE_NUM,20)
||' '||LPAD(I.INVOICE_AMOUNT,20)
||' '||LPAD(I.LINE_NUMBER ,20)
||' '||RPAD(I.Supplier ,20)
||' '||RPAD(I.SupplierSite,20)
||' '||RPAD(I.CheckNumber,20)
||' '||RPAD(I.PaymentMethod,20)
||' '||RPAD(I.BranchName,20)
||' '||RPAD(I.PaymentAmount,20)
||' '||RPAD(I.BankName ,20)
||' '||RPAD(I.BankAccount ,20)
||' '||RPAD(I.PaymentDate ,20));
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,RPAD(I.OperatingUnit,20)
||' '||RPAD(I.INVOICE_NUM,20)
||' '||LPAD(I.INVOICE_AMOUNT,20)
||' '||LPAD(I.LINE_NUMBER ,20)
||' '||RPAD(I.Supplier ,20)
||' '||RPAD(I.SupplierSite,20)
||' '||RPAD(I.CheckNumber,20)
||' '||RPAD(I.PaymentMethod,20)
||' '||RPAD(I.BranchName,20)
||' '||RPAD(I.PaymentAmount,20)
||' '||RPAD(I.BankName ,20)
||' '||RPAD(I.BankAccount ,20)
||' '||RPAD(I.PaymentDate ,20));
END LOOP;
FND_FILE.PUT_LINE(FND_FILE.LOG,'Am at End of The Loop..');
UTL_FILE.FCLOSE(F_TYPE);
END XX_AP_PROC;
END XX_AP_OUT_PKG;
--------
EXEC XX_AP_OUT_PKG.XX_AP_PROC('Vision Operations','GE Capital','DANBURY','34342','34342');

EXEC XX_AP_OUT_PKG.XX_AP_PROC('Vision Operations','Software Spectrum','SOFT SPEC');
=======================================================================================

1.create a file with the fllowing datails
ponumber,potype,suplier,suppliersite,shipto,bilto,poamount,linenum,linetyp,item,qty,price Vision Operations
----------- ------- ------- ------------- ------- 3M Health Care
input paramaters:-->Operating Unit
Supplier Name
CREATE OR REPLACE PACKAGE XX_PO_OUT_PKG
IS
PROCEDURE XX_PO_PROC(V_ERRBUF OUT VARCHAR2,V_RETCODE OUT VARCHAR2);
END XX_PO_OUT_PKG;
CREATE OR REPLACE PACKAGE BODY XX_PO_OUT_PKG
IS
PROCEDURE XX_PO_PROC (V_ERRBUF OUT VARCHAR2,V_RETCODE OUT VARCHAR2,P_OPERATINGUNIT VARCHAR2,P_SUPPLIER VARCHAR2)
IS
CURSOT C1
IS
SELECT PHA.SEGMENT1 PONUMBER
,PDTA.TYPE_NAME POTYPE
,APS.VENDOR_NAME SUPPLIER
,APSA.VENDOR_SITE_CODE SUPPLIERSITE
,HL.LOCATION_CODE SHIPTO
,HL1.LOCATION_CODE BILTO
,PLA.LINE_NUM
,PLTL.LINE_TYPE
,MSIB.SEGMENT1 ITEM
,PLA.QUANTITY
,PLA.UNIT_PRICE PRICE
,PLA.UNIT_PRICE*PLA.QUANTITY AMOUNT
FROM PO_HEADERS_ALL PHA
,PO_DOCUMENT_TYPES_ALL PDTA
,AP_SUPPLIERS APS
,AP_SUPPLIER_SITES_ALL APSA
,HR_LOCATIONS HL
,HR_LOCATIONS HL1
,PO_LINES_ALL PLA
,PO_LINE_TYPES_TL PLTL
,MTL_SYSTEM_ITEMS_B MSIB
,ORG_ORGANIZATION_DEFINITIONS OOD
,HR_OPERATING_UNITS HOU
WHERE HOU.NAME='Vision Operations' --P_OPERATINGUNIT --
AND APS.VENDOR_NAME='3M Health Care' --P_SUPPLIER --
AND PHA.TYPE_LOOKUP_CODE=PDTA.DOCUMENT_SUBTYPE
AND PHA.ORG_ID=PDTA.ORG_ID
AND PHA.VENDOR_ID=APS.VENDOR_ID
AND PHA.VENDOR_SITE_ID=APSA.VENDOR_SITE_ID
AND APS.VENDOR_ID=APSA.VENDOR_ID
AND HL.LOCATION_ID=PHA.SHIP_TO_LOCATION_ID
AND HL1.LOCATION_ID=PHA.BILL_TO_LOCATION_ID
AND PHA.PO_HEADER_ID=PLA.PO_HEADER_ID
AND PLA.LINE_TYPE_ID=PLTL.LINE_TYPE_ID
AND PLA.ITEM_ID=MSIB.INVENTORY_ITEM_ID
AND PHA.ORG_ID=MSIB.ORGANIZATION_ID
AND MSIB.ORGANIZATION_ID=OOD.ORGANIZATION_ID
AND PHA.ORG_ID=HOU.ORGANIZATION_ID;
F_TYPE UTL_FILE.FILE_TYPE;
BEGIN
F_TYPE:=UTL_FILE

plsqlrequirements

1.WRITE FUNCTION TO KNOW THE DEPTNAME BY ACCEPTILNG DEPTNO AS INPUT PARAMETER

=============================================================================

CREATE OR REPLACE FUNCTION XX_FUN2(F_DEPTNO NUMBER)

RETURN VARCHAR2

V_DNAME VARCHAR2(20);

BEGIN

SELECT DNAME INTO V_DNAME FROM DEPT WHERE DEPTNO=F_DEPTNO;

RETURN V_DNAME;

END;

--------

SQL> SELECT XX_FUN2(10) FROM DUAL;

XX_FUN2(10)

--------------------------------------------------------------------------------

ACCOUNTING

2.WRITE PROCEDURE TO KNOW THE DEPTNAME BY ACCEPTILNG DEPTNO AS INPUT PARAMETER

==============================================================================

CREATE OR REPLACE PROCEDURE XX_PROC2(F_DEPTNO NUMBER)

V_DNAME VARCHAR2(20);

BEGIN

SELECT DNAME INTO V_DNAME FROM DEPT WHERE DEPTNO=F_DEPTNO;

DBMS_OUTPUT.PUT_LINE(V_DNAME);

END;

-----

SQL> EXEC XX_PROC2(10);

ACCOUNTING

3. KNOW THE SUPPNAME FROM PROCEDURE BY ACCEPTING THE VANDERID AS IN PUT PARAMATER

======================================================================================

CREATE OR REPLACE PROCEDURE XX_VEN(V_VENID NUMBER, V_VENDOR OUT VARCHAR2)

BEGIN

SELECT VENDOR_NAME

INTO V_VENDOR

FROM AP_SUPPLIERS

WHERE VENDOR_ID=V_VENID;

DBMS_OUTPUT.PUT_LINE('SUPPNAME'||' '||V_VENDOR);

END;

----------

SQL> DECLARE

2 V_VEDER_NAME VARCHAR2(20);

3 BEGIN

4 XX_VEN(30163,V_VEDER_NAME);

5 END;

6 /

SUPPNAME TP1 Supp

(OR)

CREATE OR REPLACE PROCEDURE XX_vendor2(V_venid NUMBER)

V_VENDOR VARCHAR2(50);

BEGIN

SELECT vendor_name

INTO V_vendor

FROM ap_suppliers

WHERE vendor_id = V_venid;

dbms_output.put_line('Supplier Name : '||v_vendor);

EXCEPTION

when no_data_found then

dbms_output.put_line('Supplier Not Exist..');

END;

BEGIN

XX_Vendor2(577);

END;

4.WRITE PROCEDURE ENTER VENDOR_NAME AND DISPLAY VENDOR_ID

=============================================================

CREATE OR REPLACE PROCEDURE XX_PROC3(P_VENDOR VARCHAR2)

V_VENID NUMBER;

BEGIN

SELECT VENDOR_ID INTO V_VENID FROM AP_SUPPLIERS WHERE VENDOR_NAME=P_VENDOR;

DBMS_OUTPUT.PUT_LINE('SUPPNUM'||' '||V_VENID);

END;

---------

SQL> EXEC XX_PROC3('J1 Supp');

SUPPNUM 30164

=======================================================

5. Write the Procedure to know the count of PO's and Count of Invoice's

created for the Supplier Name

Input : Vendor Name

==========================================================================

CREATE OR REPLACE PROCEDURE XX_PROC3(P_VENDOR VARCHAR2,P_COUNT OUT NUMbER

, P_INVCOUNT OUT NUMBER)

V_VENID NUMBER;

BEGIN

SELECT VENDOR_ID

INTO V_VENID

FROM AP_SUPPLIERS

WHERE VENDOR_NAME=P_VENDOR;

SELECT COUNT(*)

INTO P_COUNT

FROM PO_HEADERS_ALL

WHERE VENDOR_ID=V_VENID;

SELECT COUNT(*)

INTO P_INVCOUNT

FROM AP_INVOICES_ALL

WHERE VENDOR_ID=V_VENID;

DBMS_OUTPUT.PUT_LINE('SUPPNUM'||' '||V_VENID);

DBMS_OUTPUT.PUT_LINE('PO COUNT'||' '||P_COUNT );

DBMS_OUTPUT.PUT_LINE('Invoice Count : '||P_INVCOUNT);

END;

------------------

SQL> DECLARE

2 V_po_COUNT NUMBER;

3 v_inv_count number;

4 BEGIN

5 XX_PROC3('3M Health Care',V_po_COUNT,v_inv_count);

6 END;

7 /

SUPPNUM 600

PO COUNT 94

Invoice Count : 81

==================OR=================

CREATE OR REPLACE PROCEDURE X_PROC(P_VENDOR VARCHAR2)

V_VEND_ID VARCHAR2(50);

P_POCOUNT NUMBER;

P_INVCOUNT NUMBER;

BEGIN

SELECT VENDOR_ID INTO V_VEND_ID FROM AP_SUPPLIERS APS WHERE APS.VENDOR_NAME=P_VENDOR;

SELECT COUNT(SEGMENT1) INTO P_POCOUNT FROM PO_HEADERS_ALL WHERE VENDOR_ID=V_VEND_ID;

SELECT COUNT(INVOICE_NUM) INTO P_INVCOUNT FROM AP_INVOICES_ALL WHERE VENDOR_ID=V_VEND_ID;

DBMS_OUTPUT.PUT_LINE('SUPPLIER ID'||' '||V_VEND_ID);

DBMS_OUTPUT.PUT_LINE('POCOUNTS ARE'||' '||P_POCOUNT);

DBMS_OUTPUT.PUT_LINE('INVCOUNTS ARE'||' '||P_INVCOUNT);

END;

===============FUNCTION==================

CREATE OR REPLACE FUNCTION XXX_FUN(P_VENDOR VARCHAR2, P_POCOUNT out NUMBER)

RETURN NUMBER

V_VENID NUMBER;

BEGIN

SELECT APS.VENDOR_ID

INTO V_VENID

FROM AP_SUPPLIERS APS

WHERE APS.VENDOR_NAME=P_VENDOR;

SELECT COUNT(SEGMENT1)

INTO P_POCOUNT

FROM PO_HEADERS_ALL PHA

WHERE PHA.VENDOR_ID=V_VENID;

DBMS_OUTPUT.PUT_LINE('Supplier ID : '||V_VENID);

DBMS_OUTPUT.PUT_LINE('PO Count : '||P_POCOUNT);

RETURN P_POCOUNT;

END;

======================================

DECLARE

V_PO_COUNT NUMBER;

v_po_cnt number;

BEGIN

V_PO_COUNT:= XXX_FUN('3M Health Care',v_po_cnt);

DBMS_OUTPUT.PUT_LINE('PO COUNT:'||V_PO_COUNT);

END;

-----------------------------------------------------------------------

CREATE OR REPLACE FUNCTION X_FUN(P_VENDOR VARCHAR2,P_POCOUNT OUT NUMBER,P_INVCOUNT OUT NUMBER)

RETURN NUMBER

V_VENDOR_ID NUMBER;

BEGIN

SELECT VENDOR_ID INTO V_VENDOR_ID FROM AP_SUPPLIERS APS WHERE APS.VENDOR_NAME=P_VENDOR;

SELECT COUNT(PHA.SEGMENT1) INTO P_POCOUNT FROM PO_HEADERS_ALL PHA WHERE PHA.VENDOR_ID=V_VENDOR_ID;

SELECT COUNT(AIA.INVOICE_NUM)INTO P_INVCOUNT FROM AP_INVOICES_ALL AIA WHERE AIA.VENDOR_ID=V_VENDOR_ID;

DBMS_OUTPUT.PUT_LINE('SUPPLIER ID'||' '||V_VENDOR_ID);

DBMS_OUTPUT.PUT_LINE('POCOUNTS ARE'||' '||P_POCOUNT);

DBMS_OUTPUT.PUT_LINE('INVCOUNTS ARE'||' '||P_INVCOUNT);

RETURN V_VENDOR_ID;

END;

---------------

DECLARE

V_PO_COUNT NUMBER;

V_INV_COUNT NUMBER;

V_TOTCOUNT NUMBER;

BEGIN

V_TOTCOUNT:=X_FUN('3M Health Care',V_PO_COUNT,V_INV_COUNT);

END;

==============OR===========

CREATE OR REPLACE FUNCTION X_FUN(P_VENDOR VARCHAR2)

RETURN NUMBER

V_VENDOR_ID NUMBER;

P_POCOUNT NUMBER;

P_INVCOUNT NUMBER;

BEGIN

SELECT VENDOR_ID INTO V_VENDOR_ID FROM AP_SUPPLIERS APS WHERE APS.VENDOR_NAME=P_VENDOR;

SELECT COUNT(PHA.SEGMENT1) INTO P_POCOUNT FROM PO_HEADERS_ALL PHA WHERE PHA.VENDOR_ID=V_VENDOR_ID;

SELECT COUNT(AIA.INVOICE_NUM)INTO P_INVCOUNT FROM AP_INVOICES_ALL AIA WHERE AIA.VENDOR_ID=V_VENDOR_ID;

DBMS_OUTPUT.PUT_LINE('SUPPLIER ID'||' '||V_VENDOR_ID);

DBMS_OUTPUT.PUT_LINE('POCOUNTS ARE'||' '||P_POCOUNT);

DBMS_OUTPUT.PUT_LINE('INVCOUNTS ARE'||' '||P_INVCOUNT);

RETURN V_VENDOR_ID;

END;

----------

SELECT X_FUN('3M Health Care')FROM DUAL;

=====================================================================================

6.. Write the Procedure to display the below details by accepting PO Number

as input parameter

PO Number, PO Type, Supplier Name, Supplier Site, Ship To, Bill To

- - - - - - - - - - - - -- - -- - - - - - - - - - - --- - - - - ------

CREATE OR REPLACE PROCEDURE XX_PROC(P_PONUMBER VARCHAR2)

V_PONUM VARCHAR2(30);

V_POTYPE VARCHAR2(30);

V_SUP_NAME VARCHAR2(50);

V_SUP_SITE NUMBER;

V_SHIPTO NUMBER;

V_BILTO NUMBER;

BEGIN

SELECT PHA.SEGMENT1 PONUM

,PHA.TYPE_LOOKUP_CODE

,APS.VENDOR_NAME

,APSSA.VENDOR_SITE_ID

,APSSA.SHIP_TO_LOCATION_ID

,APSSA.BILL_TO_LOCATION_ID

INTO V_PONUM

,V_POTYPE

,V_SUP_NAME

,V_SUP_SITE

,V_SHIPTO

,V_BILTO

FROM PO_HEADERS_ALL PHA

,AP_SUPPLIERS APS

,AP_SUPPLIER_SITES_ALL APSSA

WHERE PHA.SEGMENT1=P_PONUMBER

AND APS.VENDOR_ID=PHA.VENDOR_ID

AND APSSA.VENDOR_SITE_ID=PHA.VENDOR_SITE_ID;

DBMS_OUTPUT.PUT_LINE(V_PONUM ||' '||

V_POTYPE ||' '||

V_SUP_NAME ||' '||

V_SUP_SITE ||' '||

V_SHIPTO ||' '||

V_BILTO );

EXCEPTION

WHEN NO_DATA_FOUND THEN

DBMS_OUTPUT.PUT_LINE('THE PO HAVE A NO DATA');

END;

---------------

EXEC XX_PROC('6001');

=============================================

7.WRITE A PROCEDURE TO DISPLAY THE BELOW DETAILS FOR PASSED INPUT VALUE

OPERATINGUNIT,INVOICENUMBER,INVOICE TYPE,SUPPLIER,SUPPLIERSITE,INVOICEDATE,TERMSNAME

input parameters:=vendorname=>mandatory ....... vendor site=>optional

----------------------------------------------------

CREATE OR REPLACE PROCEDURE XX_PROC(P_VENDOR VARCHAR2)

CURSOR X_CUR

select hou.NAME operatingunit

,aia.INVOICE_NUM

,aia.INVOICE_TYPE_LOOKUP_CODE

,aps.VENDOR_NAME supplier

,apssa.VENDOR_SITE_CODE

,aia.INVOICE_DATE

,apt.NAME termsname

from ap_invoices_all aia

,ap_suppliers aps

,ap_supplier_sites_all apssa

,ap_terms apt

,hr_operating_units hou

where aps.VENDOR_NAME=P_VENDOR

and aps.VENDOR_ID=aia.VENDOR_ID

and aia.VENDOR_SITE_ID=apssa.VENDOR_SITE_ID

and aps.TERMS_ID=apt.TERM_ID

and aia.ORG_ID=hou.ORGANIZATION_ID

AND ROWNUM<=10;

BEGIN

DBMS_OUTPUT.PUT_LINE( RPAD(INITCAP('operatingunit' ),22) ||' '||

RPAD(INITCAP('INVOICENUMBER '),15) ||' '||

RPAD(INITCAP('INVOICETYPE' ),11) ||' '||

RPAD(INITCAP('supplier' ),11) ||' '||

RPAD(INITCAP('SUPPLIERSITE' ),12) ||' '||

RPAD(INITCAP('INVOICE_DATE' ),12) ||' '||

RPAD(INITCAP('termsname ' ),17));

DBMS_OUTPUT.PUT_LINE(RPAD('-',22,'-') ||' '||

RPAD('-',15,'-') ||' '||

RPAD('-',11,'-') ||' '||

RPAD('-',12,'-') ||' '||

RPAD('-',17,'-')

);

FOR I IN X_CUR

LOOP

DBMS_OUTPUT.PUT_LINE(RPAD(I.operatingunit,22) ||' '||

RPAD(I.INVOICE_NUM,15) ||' '||

RPAD(I.INVOICE_TYPE_LOOKUP_CODE,11) ||' '||

RPAD(I.supplier ,11) ||' '||

RPAD(I.VENDOR_SITE_CODE,12) ||' '||

RPAD(I.INVOICE_DATE ,12) ||' '||

RPAD(I.termsname ,17) );

END LOOP;

END;

==================================================================================

8. write a function to display the below details

operating unit ,requisition number,requisitionstype,prepare,linenum,

linetype,item,quantity,price

--input:operatingunit---manadatory

from requisition number---manadtory

to requisition number---optional

select * from po_requisition_headers_all prha

------------------------------------------------------

CREATE OR REPLACE FUNCTION ZZ_FUN(P_OPERATING VARCHAR2,p_from_reqnum varchar2,p_to_req_num varchar2)

RETURN VARCHAR2

A NUMBER := 0;

BEGIN

DBMS_OUTPUT.PUT_LINE(RPAD(INITCAP('OPEERATINGUNIT'),17) ||

LPAD(RPAD(INITCAP('REQNUM'),6),7) ||

LPAD(RPAD(INITCAP('REQTYPE'),8),9) ||

LPAD(RPAD(INITCAP('PREPARE'),20),21) ||

LPAD(RPAD(INITCAP('LINE NUM'),8),9) ||

LPAD(RPAD(INITCAP('ITEM'),6),7) ||

LPAD(RPAD(INITCAP('LINE_TYPE'),11),12)||

LPAD(RPAD(INITCAP('QUANTITY '),9),10) ||

LPAD(RPAD(INITCAP('UNIT_PRICE'),10),11));

DBMS_OUTPUT.PUT_LINE(RPAD('-',17,'-') ||

LPAD(RPAD('-',6,'-'),7) ||

LPAD(RPAD('-',8,'-'),9) ||

LPAD(RPAD('-',20,'-'),21)||

LPAD(RPAD('-',8,'-'),9) ||

LPAD(RPAD('-',6,'-'),7) ||

LPAD(RPAD('-',11,'-'),12)||

LPAD(RPAD('-',9,'-'),10) ||

LPAD(RPAD('-',10,'-'),11));

FOR I IN (SELECT HOU.NAME OPEERATINGUNIT,

PRHA.SEGMENT1 REQNUM,

PRHA.TYPE_LOOKUP_CODE REQTYPE,

PAPF.FULL_NAME PREPARE,

PRLA.LINE_NUM,

MSIB.SEGMENT1 ITEM,

PLTL.LINE_TYPE,

PRLA.QUANTITY,

PRLA.UNIT_PRICE

FROM HR_OPERATING_UNITS HOU,

PO_REQUISITION_HEADERS_ALL PRHA,

PO_REQUISITION_LINES_ALL PRLA,

PO_REQ_DISTRIBUTIONS_ALL PRDA,

MTL_SYSTEM_ITEMS_B MSIB,

PER_ALL_PEOPLE_F PAPF,

PO_DISTRIBUTIONS_ALL PDA,

PO_LINE_LOCATIONS_ALL PLLA,

PO_LINES_ALL PLA,

PO_HEADERS_ALL PHA ,

PO_LINE_TYPES_TL PLTL

WHERE HOU.NAME=P_OPERATING

AND PRHA.REQUISITION_HEADER_ID=PRLA.REQUISITION_HEADER_ID

AND PRLA.REQUISITION_LINE_ID=PRDA.REQUISITION_LINE_ID

AND PRDA.DISTRIBUTION_ID=PDA.REQ_DISTRIBUTION_ID

AND PDA.LINE_LOCATION_ID=PLLA.LINE_LOCATION_ID

AND PLLA.PO_LINE_ID=PLA.PO_LINE_ID

AND PLA.PO_HEADER_ID=PHA.PO_HEADER_ID

AND PHA.AGENT_ID=PAPF.PERSON_ID

AND PLA.ITEM_ID=MSIB.INVENTORY_ITEM_ID

AND PHA.ORG_ID=MSIB.ORGANIZATION_ID

AND PHA.ORG_ID=HOU.ORGANIZATION_ID

AND PLTL.LINE_TYPE_ID=PRLA.LINE_TYPE_ID

AND PRHA.ORG_ID=204

AND ROWNUM<=15

AND PRHA.SEGMENT1 BETWEEN p_from_reqnum AND nvl(p_to_req_num,PRHA.SEGMENT1))

LOOP

DBMS_OUTPUT.PUT_LINE(RPAD(I.OPEERATINGUNIT,17)||' '||

RPAD(I.REQNUM ,6) ||' '||

RPAD(I.REQTYPE ,8) ||' '||

RPAD(I.PREPARE ,20) ||' '||

LPAD(I.LINE_NUM ,8) ||' '||

RPAD(I.ITEM ,6) ||' '||

RPAD(I.LINE_TYPE ,12) ||' '||

LPAD( I.QUANTITY ,8) ||' '||

LPAD(I.UNIT_PRICE,10 ));

A :=A+1;

END LOOP;

RETURN A;

END;

========================================================================

9.WRITE A PACKAGE IN THAT

WRITE A PROCEDURE TO DISPLAY THE DETAILS AS FOLLOWS

operatingunit,ponumber,potype,supplier,supplier site,shipto,bilto,buyer

linenum,linetype,item,quantity,price,totalprice

paramater::suppliername---manadatory

---------------------------------------

WRITE A FUNCTION TO DISPLAY THE DETAILS AS FOLLOWS

operatingunit,ordernumber,ordertype,pricelist,customername,shiptoaddresss,biltoaddress

linenum,linetype,item,quantity,price,totalprice,terms,name

paramater:customername---manadatory

fromorder number--optional

to order number--optional

----------------------------------------

CREATE OR REPLACE PACKAGE XXX_PAG

PROCEDURE ZZ_PROC(P_SUPLIERNAME VARCHAR2);

FUNCTION ZZ_FUN(P_OPERATING VARCHAR2)RETURN VARCHAR2;

END;

CREATE OR REPLACE PACKAGE BODY XXX_PAG

PROCEDURE ZZ_PROC(P_SUPLIERNAME VARCHAR2)

cursor c1 is

SELECT PHA.SEGMENT1 PONUM,

PHA.TYPE_LOOKUP_CODE,

APS.VENDOR_NAME SUPPLIER,

APSSA.VENDOR_SITE_CODE SUPPLIERSITE,

APSSA.SHIP_TO_LOCATION_ID SHIPTO,

APSSA.BILL_TO_LOCATION_ID BILTO,

PAPF.FULL_NAME BUYER,

PLA.LINE_NUM,

PLTL.LINE_TYPE,

MSIB.SEGMENT1 ITEM,

PLA.QUANTITY,

PLA.UNIT_PRICE

FROM AP_SUPPLIERS APS ,

PO_HEADERS_ALL PHA ,

AP_SUPPLIER_SITES_ALL APSSA,

PO_LINES_ALL PLA,

PO_LINE_TYPES_TL PLTL,

MTL_SYSTEM_ITEMS_B MSIB,

PER_ALL_PEOPLE_F PAPF

WHERE APS.VENDOR_NAME=P_SUPLIERNAME

AND APS.VENDOR_ID=PHA.VENDOR_ID

AND PHA.VENDOR_SITE_ID=APSSA.VENDOR_SITE_ID

AND PHA.PO_HEADER_ID=PLA.PO_HEADER_ID

AND PLA.LINE_TYPE_ID=PLTL.LINE_TYPE_ID

AND PLA.ITEM_ID=MSIB.INVENTORY_ITEM_ID

AND PHA.ORG_ID=MSIB.ORGANIZATION_ID

AND PHA.AGENT_ID=PAPF.PERSON_ID;

BEGIN

FOR I IN C1

LOOP

DBMS_OUTPUT.PUT_LINE('DETAILS:' ||' '||

I.PONUM ||' '||

I.TYPE_LOOKUP_CODE||' '||

I.SUPPLIER ||' '||

I.SUPPLIERSITE ||' '||

I.SHIPTO ||' '||

I.BILTO ||' '||

I.BUYER ||' '||

I.LINE_NUM ||' '||

I.LINE_TYPE ||' '||

I.ITEM ||' '||

I.QUANTITY ||' '||

I.UNIT_PRICE );

END LOOP;

EXCEPTION

WHEN NO_DATA_FOUND THEN

DBMS_OUTPUT.PUT_LINE('THERE IS NO DATA');

END ZZ_PROC;

FUNCTION ZZ_FUN(P_CUSTOMER VARCHAR2)

RETURN VARCHAR2

CURSOR C1 IS

SELECT HOU.NAME OPERATING

,OOHA.ORDER_NUMBER

,OTTT.NAME ORDERTYPE

,QLH.NAME PRICELIST

,HP.PARTY_NAME CUSTOMERNAME

,(HL.ADDRESS1||','||HL.CITY||','||HL.STATE||','||HL.POSTAL_CODE||','||HL.COUNTRY)SHIPTOADDRESS

,(HL.ADDRESS1||','||HL.CITY||','||HL.STATE||','||HL.POSTAL_CODE||','||HL.COUNTRY)BILTOADDRESS

,OOLA.LINE_NUMBER

,OOLA.ORDERED_ITEM ITEM

,OOLA.ORDERED_QUANTITY QUANTITY

,OOLA.UNIT_SELLING_PRICE PRICE

,OOLA.LINE_CATEGORY_CODE

,RTA.NAME TERMSNAME

,OOLA.ORDERED_QUANTITY*OOLA.UNIT_SELLING_PRICE TOTAL_PRICE

FROM OE_ORDER_HEADERS_ALL OOHA

,OE_TRANSACTION_TYPES_TL OTTT

,QP_LIST_HEADERS QLH

,HR_OPERATING_UNITS HOU

,HZ_PARTIES HP

,HZ_CUST_ACCOUNTS HCA

,HZ_CUST_SITE_USES_ALL HCSUA

,HZ_LOCATIONS HL

,HZ_PARTY_SITES HPS

,HZ_CUST_ACCT_SITES_ALL HCASA

,OE_ORDER_LINES_ALL OOLA

,RA_CUSTOMER_TRX_ALL RCTA

,RA_TERMS_TL RTA

WHERE HP.PARTY_NAME=P_CUSTOMER

AND QLH.LIST_HEADER_ID=OOHA.PRICE_LIST_ID

AND OOHA.ORG_ID=HOU.ORGANIZATION_ID

AND OOHA.ORDER_TYPE_ID=OTTT.TRANSACTION_TYPE_ID

AND HP.PARTY_ID=HCA.PARTY_ID

AND HCA.CUST_ACCOUNT_ID=OOHA.SOLD_TO_ORG_ID

AND HCSUA.SITE_USE_ID=OOHA.SHIP_TO_ORG_ID

AND HL.LOCATION_ID=HPS.LOCATION_ID

AND HPS.PARTY_SITE_ID=HCASA.PARTY_SITE_ID

AND HCASA.CUST_ACCT_SITE_ID=HCSUA.CUST_ACCT_SITE_ID

AND OOHA.HEADER_ID=OOLA.HEADER_ID

AND OOHA.ORDER_TYPE_ID=OTTT.TRANSACTION_TYPE_ID

AND TO_CHAR(OOHA.ORDER_NUMBER)=RCTA.INTERFACE_HEADER_ATTRIBUTE1

AND RCTA.TERM_ID=RTA.TERM_ID

AND ROWNUM<=10;

BEGIN

DBMS_OUTPUT.PUT_LINE( RPAD(INITCAP( 'OPERATING'),18) ||' '||

RPAD(INITCAP( 'ORDER NUM' ),10) ||' '||

RPAD(INITCAP ('ORDERTYPE' ),14) ||' '||

RPAD(INITCAP( 'PRICELIST' ),15) ||' '||

RPAD(INITCAP( 'CUSTOMERNAME'),15) ||' '||

RPAD(INITCAP('SHIPTOADDRESS'),36) ||' '||

RPAD(INITCAP('BILTOADDRESS' ),36) ||' '||

RPAD(INITCAP( 'LINENUM' ),8) ||' '||

RPAD(INITCAP('ITEM' ),10) ||' '||

RPAD(INITCAP('QUANTITY'),10) ||' '||

RPAD(INITCAP('PRICE ' ),6) ||' '||

RPAD(INITCAP( 'TERMSNAME' ),9) ||' '||

RPAD(INITCAP( 'TOTAL PRICE '),10));

DBMS_OUTPUT.PUT_LINE(RPAD('-',18,'-')||' '||

RPAD('-',10,'-')||' '||

RPAD('-',14,'-')||' '||

RPAD('-',15,'-')||' '||

RPAD('-',36,'-')||' '||

RPAD('-',8,'-') ||' '||

RPAD('-',10,'-')||' '||

RPAD('-',6,'-') ||' '||

RPAD('-',9,'-' )||' '||

RPAD('-',11,'-'));

FOR I IN C1

LOOP

DBMS_OUTPUT.PUT_LINE(

RPAD(I.OPERATING,18) ||' '||

RPAD(I.ORDER_NUMBER,10) ||' '||

RPAD(I.ORDERTYPE,15) ||''||

RPAD(I.PRICELIST,16) ||''||

RPAD(I.CUSTOMERNAME,15)||' '||

RPAD(I.SHIPTOADDRESS,36)||' '||

RPAD(I.BILTOADDRESS,36)||' '||

LPAD(I.LINE_NUMBER,8) ||' '||

RPAD(I.ITEM,10) ||' '||

LPAD(I.QUANTITY,10) ||' '||

LPAD(I.PRICE ,6) ||' '||

RPAD(I.TERMSNAME,9) ||' '||

LPAD(I.TOTAL_PRICE ,11));

END LOOP;

RETURN 'A';

END ZZ_FUN;

END XXX_PAG;

OUTPUTON

============

1.EXEC XXX_PAG.ZZ_PROC('3M Health Care');

2.SELECT XXX_PAG.ZZ_FUN('A. C. Networks')FROM DUAL;

=============================================================

13.write function to de below tranctions

input parameters:operating unit

supplier

po number

insert the below details into po custom table

OPERATING UNIT,PO NUMBER,TYPE,SUPPLIER,SITE,LINE NUM,LINE TYPE,ITEM, QUANTITY,

PRICE,TOTAL PRICE,CODE COMBAINATION(i.e CONCATINATIED SEGMENT)

if the po having requiaition,

insert the requisition details into requisition custom table

OPERATING UNIT,REQ NUMBER,TYPE,PREPARER,LINUM,LINE TYPE,ITEM,QUANTITIY,PRICE,

TOTAL PRICE,CODE COMBINATIONS(i.e concatinated segment)

CREATE TABLE PO_CUSTOM_TABLE

(OPERATING_UNIT VARCHAR(100),

PO_NUMBER VARCHAR(100),

TYPE_NAME VARCHAR(100),

SUPPLIER VARCHAR(100),

SUPPLIER_SITE VARCHAR(100),

LINE_NUM NUMBER,

LINE_TYPE VARCHAR(100),

ITEM VARCHAR(100),

QUANTITY NUMBER,

PRICE NUMBER,

TOTAL_PRICE NUMBER,

CODE_COMBAINATION VARCHAR(100),

REQ_DISTRIBUTIONS_ID NUMBER);

CREATE TABLE REQUISITION_CUSTOM_TABLE

(OPERATING_UNIT VARCHAR(100),

REQ_NUMBER VARCHAR(100),

TYPE_NAME VARCHAR(100),

LINE_NUM NUMBER,

LINE_TYPE VARCHAR(100),

PREPARE VARCHAR(100),

ITEM VARCHAR2(100),

QUANTITY NUMBER,

UNIT_PRICE NUMBER,

TOTAL_PRICE NUMBER,

CODE_COMBANATION VARCHAR(100));

-------------------------------------------------------------------

CREATE OR REPLACE PROCEDURE PP_PROC(P_OPERATINGINIT VARCHAR2,P_SUPPLIER VARCHAR2,P_PONUM VARCHAR2)

CURSOR XX_CUR IS

SELECT HOU.NAME OPERATINGUNIT

,PHA.SEGMENT1 PONUM

,PDTA.TYPE_NAME

,APS.VENDOR_NAME SUPPLIER

,APSS.VENDOR_SITE_CODE

,PLA.LINE_NUM

,PLTT.LINE_TYPE

,MSIB.SEGMENT1 ITEM

,PLA.QUANTITY

,PLA.UNIT_PRICE

,PLA.QUANTITY*PLA.UNIT_PRICE TOTALPRICE

,GCCK.CONCATENATED_SEGMENTS

,PDA.REQ_DISTRIBUTION_ID

FROM PO_HEADERS_ALL PHA

,PO_LINES_ALL PLA

,PO_LINE_LOCATIONS_ALL PLLA

,PO_DISTRIBUTIONS_ALL PDA

,PO_DOCUMENT_TYPES_ALL PDTA

,PO_LINE_TYPES_TL PLTT

,AP_SUPPLIERS APS

,AP_SUPPLIER_SITES_ALL APSS

,GL_CODE_COMBINATIONS_KFV GCCK

,HR_OPERATING_UNITS HOU

,MTL_SYSTEM_ITEMS_B MSIB

WHERE HOU.NAME=P_OPERATINGINIT

AND APS.VENDOR_NAME=P_SUPPLIER

AND PHA.SEGMENT1=P_PONUM

AND PHA.PO_HEADER_ID=PLA.PO_HEADER_ID

AND PLA.PO_LINE_ID=PLLA.PO_LINE_ID

AND PLLA.LINE_LOCATION_ID=PDA.LINE_LOCATION_ID

AND PHA.TYPE_LOOKUP_CODE=PDTA.DOCUMENT_SUBTYPE

AND PLA.LINE_TYPE_ID=PLTT.LINE_TYPE_ID

AND PHA.ORG_ID=PDTA.ORG_ID

AND PHA.VENDOR_ID=APS.VENDOR_ID

AND PHA.VENDOR_SITE_ID=APSS.VENDOR_SITE_ID

AND PDA.CODE_COMBINATION_ID=GCCK.CODE_COMBINATION_ID

AND PHA.ORG_ID=HOU.ORGANIZATION_ID

AND MSIB.INVENTORY_ITEM_ID=PLA.ITEM_ID

AND MSIB.ORGANIZATION_ID=PLA.ORG_ID ;

CURSOR XX_REQ( REQ_NUM VARCHAR2) IS

SELECT HOU.NAME OPERATINGUNIT

,PRHA.SEGMENT1 REQNUM

,PDTA.TYPE_NAME

,PRLA.LINE_NUM

,PLTT.LINE_TYPE

,PAPF.FULL_NAME PREPARER

,MSIB.SEGMENT1 ITEM

,PRLA.QUANTITY

,PRLA.UNIT_PRICE

,PRLA.QUANTITY*PRLA.UNIT_PRICE TOTALPRICE

,GCCK.CONCATENATED_SEGMENTS

FROM PO_REQUISITION_HEADERS_ALL PRHA

,PO_REQUISITION_LINES_ALL PRLA

,PO_REQ_DISTRIBUTIONS_ALL PRDA

,PO_DOCUMENT_TYPES_ALL PDTA

,PO_LINE_TYPES_TL PLTT

,PER_ALL_PEOPLE_F PAPF

,GL_CODE_COMBINATIONS_KFV GCCK

,MTL_SYSTEM_ITEMS_B MSIB

,HR_OPERATING_UNITS HOU

WHERE PRDA.DISTRIBUTION_ID=REQ_NUM

AND PRHA.REQUISITION_HEADER_ID=PRLA.REQUISITION_HEADER_ID

AND PRLA.REQUISITION_LINE_ID=PRDA.REQUISITION_LINE_ID

AND PRHA.TYPE_LOOKUP_CODE=PDTA.DOCUMENT_SUBTYPE

AND PRHA.ORG_ID=PDTA.ORG_ID

AND PRLA.LINE_TYPE_ID=PLTT.LINE_TYPE_ID

AND PRDA.CODE_COMBINATION_ID=GCCK.CODE_COMBINATION_ID

AND PRLA.ITEM_ID=MSIB.INVENTORY_ITEM_ID

AND PRLA.ORG_ID=MSIB.ORGANIZATION_ID

AND PRHA.PREPARER_ID=PAPF.PERSON_ID

AND PRHA.ORG_ID=HOU.ORGANIZATION_ID;

BEGIN

FOR I IN XX_CUR

LOOP

INSERT INTO po_custom_table VALUES( I.OPERATINGUNIT

,I.PONUM

,I.TYPE_NAME

,I.SUPPLIER

,I.VENDOR_SITE_CODE

,I.LINE_NUM

,I.LINE_TYPE

,I.ITEM

,I.QUANTITY

,I.UNIT_PRICE

,I.TOTALPRICE

,I.CONCATENATED_SEGMENTS

,I.REQ_DISTRIBUTION_ID

);

FOR J IN XX_REQ(I.REQ_DISTRIBUTION_ID)

LOOP

INSERT INTO requisition_custom_table VALUES(J.OPERATINGUNIT

,J.REQNUM

,J.TYPE_NAME

,J.LINE_NUM

,J.LINE_TYPE

,J.PREPARER

,J.ITEM

,J.QUANTITY

,J.UNIT_PRICE

,J.TOTALPRICE

,J.CONCATENATED_SEGMENTS);

END LOOP;

COMMIT;

END;

---------------------------------

SELECT * FROM HR_OPERATING_UNITS;

SELECT * FROM AP_SUPPLIERS WHERE VENDOR_ID=600

SELECT * FROM PO_CUSTOM_TABLE;

SELECT * FROM REQUISITION_CUSTOM_TABLE;

TRUNCATE TABLE PO_CUSTOM_TABLE;

TRUNCATE TABLE REQUISITION_CUSTOM_TABLE;

DROP TABLE PO_CUSTOM_TABLE;

DROP TABLE REQUISITION_CUSTOM_TABLE;

DESC REQUISITION_CUSTOM_TABLE;

EXEC PP_PROC('Progress UK','Office Supplies, Inc.','650')

======================================================================