Registering PL SQL Procedures Concurrent Programs in Oracle

1)Develop C.P which will transfer the Approved Purchase orders which
are 'STANDARD' type into temp tables

po_temp_headers
===============
po_num, buyer, shipto, billto, currency, supplier, site, contactname

po_temp_lines
==============
pono,lineno,item,desc,uom.category,linetotal(quantity*unitprice)

No Parameters:
==============
1)Create Temp Tables
2)Cursors 1)headers
2)lines
3)Open the cursor for loop
4)Two Insert statments 1)to insert into Headers table
2)To Insert into Line table
5)Close for loop
6)Register Procedure as C.P

2)Develop C.P Which will transfer the follwoing data into Temp Tables

Supplier Name:

Quoteno type Cdate Quoteamount shipto Billto Buyer
====== ==== ===== =========== ====== ====== =====

quotation_temp_table
====================
quoteno,type,cdate,qamount,Shipto,Billto,Buyer

Sol:
=====
Parameter:
Supplier Name : Table value set (Internally Pass VendorID)

1)Create Temp Table
2)Cursor to retrieve the quotation which are created for specified
supplier(Pass parameter)
3)open the cursor
4)Insert statement into temp table
5)Close cursor
6)End procedure and register as C.P

3)From Date
To Date
Program should bring

no 'STANDARD' po's we have created ,
PO Total amount,
total approved po's,
total not approved po's
details insert temp table

PO_TOTAL_DETAILS
================
no_of_po number(8),
po_total_amt number(10),
no_of_approved_po number(8),
no_of_not_approved_po number(8).


Sol 2 Program:
=============
CREATE OR REPLACE Procedure data_transfer_Quote(Errbuf OUT varchar2,
Retcode Out varchar2,
v_id IN number) as
cursor c1 is SELECT PHA.SEGMENT1 QUOTENO,
InitCap(PHA.QUOTE_TYPE_LOOKUP_CODE||'-'||PHA.TYPE_LOOKUP_CODE) QUOTETYPE,
Trunc(PHA.CREATION_DATE) CDATE,
SUM(PLA.UNIT_PRICE) QUOTEAMT,
H1.LOCATION_CODE SHIPTO,
H2.LOCATION_CODE BILLTO,
PPF.FULL_NAME BUYER
FROM PO_HEADERS_ALL PHA,
PO_LINES_ALL PLA,
hr_locations H1,
HR_LOCATIONS H2,
PER_ALL_PEOPLE_F PPF
WHERE PHA.TYPE_LOOKUP_CODE = 'QUOTATION'
AND PHA.PO_HEADER_ID = PLA.PO_HEADER_ID
and PHA.SHIP_TO_LOCATION_ID = H1.LOCATION_ID
AND PHA.BILL_TO_LOCATION_ID = H2.LOCATION_ID
AND PHA.AGENT_ID = PPF.PERSON_ID
AND pha.vendor_id = v_id
GROUP BY
PHA.SEGMENT1 ,
InitCap(PHA.QUOTE_TYPE_LOOKUP_CODE||'-'||PHA.TYPE_LOOKUP_CODE) ,
Trunc(PHA.CREATION_DATE),
H1.LOCATION_CODE,
H2.LOCATION_CODE,
PPF.FULL_NAME;
l_count number(9) default 0;
Begin
delete from temp_quote;
commit;
For C2 in C1 Loop
l_count := l_count+1;
Insert into temp_quote(quoteno,
quotetype,
cdate,
quoteamt,
shipto,
billto,
buyer)
values (c2.QUOTENO,
c2.QUOTETYPE,
c2.CDATE,
c2.QUOTEAMT,
c2.shipto,
c2.billto,
c2.buyer);
End Loop;
Commit;
Fnd_File.Put_line(Fnd_File.Output ,'No of Records transfered into Table : '||l_count);
Exception
when Others then
Fnd_File.Put_line(Fnd_File.Log ,'Error Occured during Data Insertion');
End data_transfer_Quote;
/


Solution1:
----------
Create Or Replace Procedure po_data(Errbuf Out varchar2,
Retcode OUT varchar2) as

Cursor c1 is SELECT PHA.SEGMENT1 PONUM,
PPF.FULL_NAME BUYER,
HR1.LOCATION_CODE ShipTo,
HR2.LOCATION_CODE BillTo,
pha.CURRENCY_CODE ccode ,
pv.vendor_name supplier,
pvs.vendor_site_code site,
(pvc.LAST_NAME||','||pvc.FIRST_NAME) Contact
FROM PO_HEADERS_ALL PHA,
PER_ALL_PEOPLE_F PPF,
HR_LOCATIONS HR1,
HR_LOCATIONS HR2,
po_vendors pv,
po_vendor_sites_all pvs,
po_vendor_contacts pvc
WHERE PHA.TYPE_LOOKUP_CODE ='STANDARD'
AND UPPER(PHA.AUTHORIZATION_STATUS) ='APPROVED'
AND PHA.AGENT_ID =PPF.PERSON_ID
AND PHA.SHIP_TO_LOCATION_ID =HR1.LOCATION_ID
AND PHA.BILL_TO_LOCATION_ID =HR2.LOCATION_ID
and pha.vendor_id =pv.vendor_id
and pha.vendor_site_id =pvs.VENDOR_SITE_ID
and pha.VENDOR_CONTACT_ID =pvc.VENDOR_CONTACT_ID;
Cursor c2 is SELECT PHA.SEGMENT1 PONUM,
PLA.LINE_NUM lINENO ,
MSI.SEGMENT1 ITEM,
MSI.DESCRIPTION ITEMDESC,
MSI.PRIMARY_UOM_CODE UOM,
(MC.SEGMENT1||','||MC.SEGMENT2) Category ,
(pla.QUANTITY*pla.UNIT_PRICE) linetotal
FROM PO_HEADERS_ALL PHA,
PO_LINES_ALL PLA,
MTL_SYSTEM_ITEMS_B MSI,
MTL_CATEGORIES MC
WHERE PHA.PO_HEADER_ID =PLA.PO_HEADER_ID
AND PHA.TYPE_LOOKUP_CODE ='STANDARD'
AND UPPER(PHA.AUTHORIZATION_STATUS) ='APPROVED'
AND MSI.INVENTORY_ITEM_ID =PLA.ITEM_ID
AND MSI.ORGANIZATION_ID =PLA.ORG_ID
AND PLA.CATEGORY_ID =MC.CATEGORY_ID;
Begin
For x1 in c1 loop
Insert into po_temp_headers
values(x1.ponum,
x1.buyer,
x1.shipto,
x1.billto,
x1.ccode,
x1.supplier,
x1.site,
x1.contact);
End Loop;
Commit;
For x2 in c2 loop
Insert into po_temp_lines
values(x2.ponum,
x2.lineno,
x2.item,
x2.itemdesc,
x2.uom,
x2.category,
x2.linetotal);
End Loop;
Commit;
Exception
when others then
Fnd_File.Put_line(Fnd_File.Log,'Error Occured during data Insertion');
End;


Solution3:
==========

CREATE OR REPLACE Procedure PO_count(Errbuf OUT varchar2,
Retcode OUT varchar2,
fdate in date,
tdate in date) as
l_total_pos number(9);
l_total_amt number(9,2);
l_total_approved number(9);
l_total_not_approved number(9);
Begin
DELETE FROM PO_TOTAL_DETAILS;
COMMIT;
SELECT Count(po_header_id)
INTO l_total_pos
FROM PO_HEADERS_ALL
WHERE Trunc(Creation_date) between fdate and tdate
AND TYPE_LOOKUP_CODE = 'STANDARD';
SELECT sum(pla.QUANTITY * pla.UNIT_PRICE)
INTO l_total_amt
FROM PO_HEADERS_ALL PHA,
PO_LINES_ALL PLA
WHERE Trunc(Pha.Creation_date) between fdate and tdate
and pha.type_lookup_code = 'STANDARD'
and pha.po_header_id = pla.po_header_id;
SELECT count(*)
INTO l_total_approved
FROM PO_HEADERS_ALL pha
WHERE AUTHORIZATION_STATUS = 'APPROVED'
and Trunc(Pha.Creation_date) between fdate and tdate
and pha.type_lookup_code = 'STANDARD';
l_total_not_approved :=l_total_pos - l_total_approved;
INSERT INTO PO_TOTAL_DETAILS
VALUES( l_total_pos,
l_total_amt,
l_total_approved,
l_total_not_approved);
Commit;
Exception
When Others then
Fnd_File.Put_line(Fnd_File.Log,'Error Occured during Data Inserting');
End;
/