1.WRITE FUNCTION TO KNOW THE DEPTNAME BY ACCEPTILNG DEPTNO AS INPUT PARAMETER
=============================================================================
CREATE OR REPLACE FUNCTION XX_FUN2(F_DEPTNO NUMBER)
RETURN VARCHAR2
IS
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)
IS
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)
IS
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)
IS
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)
IS
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)
IS
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)
IS
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
IS
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
IS
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
IS
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)
IS
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)
IS
CURSOR X_CUR
IS
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('-',11,'-') ||' '||
RPAD('-',12,'-') ||' '||
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
IS
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
IS
PROCEDURE ZZ_PROC(P_SUPLIERNAME VARCHAR2);
FUNCTION ZZ_FUN(P_OPERATING VARCHAR2)RETURN VARCHAR2;
END;
CREATE OR REPLACE PACKAGE BODY XXX_PAG
IS
PROCEDURE ZZ_PROC(P_SUPLIERNAME VARCHAR2)
IS
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
IS
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('-',15,'-')||' '||
RPAD('-',36,'-')||' '||
RPAD('-',36,'-')||' '||
RPAD('-',8,'-') ||' '||
RPAD('-',10,'-')||' '||
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)
IS
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;
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')
======================================================================
No comments:
Post a Comment