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
