Declare
p_org_id po_requisition_headers_all.ORG_ID%TYPE :=&&1;
p_pr_no po_requisition_headers_all.segment1%TYPE :='&&2';
p_Requisition_header_id po_requisition_headers_all.Requisition_header_id%TYPE;
v_err_msg VARCHAR2(255) := NULL;
v_ora_err_msg_desc VARCHAR2(255) := NULL;
v_ora_err_msg_code VARCHAR2(255) := NULL;
vld_error EXCEPTION;
CURSOR c_pr_cursor (p_req_no Number) is
Select SUBSTR(prh.Requisition_header_id,1,21) as Requisition_header_id
,SUBSTR(prh.SEGMENT1,1,9) as SEGMENT1
,SUBSTR(prh.AUTHORIZATION_STATUS,1,19) as AUTHORIZATION_STATUS
,prh.interface_source_code
,SUBSTR(prl.line_num,1,9) as line_num
,SUBSTR(msib.Segment2,1,15) as INV_ITEM
,SUBSTR(prl.ITEM_DESCRIPTION,1.50) as ITEM_DESCRIPTION
,MCB.SEGMENT2||'.'||MCB.SEGMENT4||'.'||MCB.SEGMENT3 as ITEM_CATEGORY
,prl.UNIT_MEAS_LOOKUP_CODE
,prl.need_by_date
,SUBSTR(pv.vendor_name,1,20) as vendor_name
,pvsa.vendor_site_code
,ph.segment1 as PO_NUMBER
,pl.line_num as PO_LINE_NUM
,(select name
from apps.hr_operating_units
where organization_id = nvl(prh.ORG_ID,-99)) as ORGANIZATION_NAME
,prh.org_id
FROM apps.po_requisition_headers_all Prh
,apps.po_requisition_lines_all Prl
,apps.MTL_SYSTEM_ITEMS_B msib
,apps.PO_VENDORS pv
,apps.PO_VENDOR_SITES_ALL pvsa
,apps.po_lines_all pl
,apps.po_headers_all ph
,apps.MTL_CATEGORIES_B mcb
Where prh.Requisition_header_id = p_req_no
--and prh.org_id = p_org_id
and prh.Requisition_header_id = prl.Requisition_header_id
and prh.org_id = prl.org_id
and prl.BLANKET_PO_HEADER_ID = ph.po_header_id (+)
and prl.org_id = ph.org_id (+)
and pl.po_header_id (+)= ph.po_header_id
and pl.org_id (+)= ph.org_id
and Msib.INVENTORY_ITEM_ID (+) = Prl.item_id
and msib.ORGANIZATION_ID (+) = prl.DESTINATION_ORGANIZATION_ID
and pv.vendor_id (+) = prl.vendor_id
and pvsa.vendor_site_id (+) = prl.vendor_site_id
and mcb.CATEGORY_ID = prl.CATEGORY_ID
and prl.vendor_id is null
and prl.BLANKET_PO_HEADER_ID is null
and Prl.item_id is not null
and prl.DOCUMENT_TYPE_CODE is null
and prh.interface_source_code is null
order by prh.SEGMENT1
;
Cursor C_history(p_req_no Number) is
Select pah.OBJECT_TYPE_CODE
,pah.OBJECT_SUB_TYPE_CODE
,pah.ACTION_CODE
,pf.FULL_NAME as EMPLOYEE_NAME
from apps.PO_ACTION_HISTORY pah
,apps.per_all_people_f pf
where pah.OBJECT_ID = p_req_no
and pah.OBJECT_TYPE_CODE = 'REQUISITION'
and pf.PERSON_ID = pah.employee_id
;
Cursor C_App_SL(p_inv_item varchar2) is
Select paa.ASL_ID
,pv.vendor_name
,pvs.vendor_site_code
,msib.SEGMENT2 AS INVENTORY_ITEM
,pad.DOCUMENT_TYPE_CODE
,pha.segment1 AS PO_NUMBER
,pha.start_date
,pha.end_date
,pla.line_num
,(select organization_name
from apps.org_organization_definitions
where organization_id = nvl(pasl.OWNING_ORGANIZATION_ID,-99)) as ORGANIZATION_NAME
from apps.po_approved_supplier_list pasl,
apps.po_asl_attributes paa,
apps.PO_ASL_DOCUMENTS pad,
apps.PO_VENDORS pv,
apps.PO_VENDOR_SITES_ALL pvs,
apps.po_headers_all pha,
apps.po_lines_all pla,
apps.mtl_system_items_b msib
where pv.vendor_id = pasl.vendor_id
and paa.ASL_ID = pasl.ASL_ID
and pad.DOCUMENT_HEADER_ID = pha.po_header_id
and pad.DOCUMENT_LINE_ID = pla.po_line_id
and paa.ASL_ID = pad.ASL_ID
and pasl.vendor_site_id = pvs.vendor_site_id
and pasl.ITEM_ID = msib.inventory_item_id
and pasl.OWNING_ORGANIZATION_ID = msib.ORGANIZATION_ID
and msib.SEGMENT2 = p_inv_item --:v_inv_id
;
Cursor C_SR_ASR(p_vendor varchar2
,p_inv_item1 varchar2)
is
Select distinct msr.SOURCING_RULE_NAME
,msr.DESCRIPTION
,msr.status
,msr.PLANNING_ACTIVE
,msro.EFFECTIVE_DATE
,pv.vendor_name
,pvs.vendor_site_code
,msso.ALLOCATION_PERCENT
,msso.RANK as RANK1
,DECODE (msso.SOURCE_TYPE,'1','Transfer From','3','Buy From') as TYPE1
,MSS.ASSIGNMENT_SET_NAME
,msa.SOURCING_RULE_TYPE
,msa.SOURCING_RULE_TYPE_TEXT
,msa.ASSIGNMENT_TYPE
,msib.segment2 as INV_ITEM
,DECODE (msa.ASSIGNMENT_TYPE,'6','Item Organization','3','Item','4','Organization','1','Global') as ASSIGNED_TO
from apps.MRP_SOURCING_RULES msr,
apps.MRP_SR_RECEIPT_ORG msro,
apps.MRP_SR_SOURCE_ORG msso,
apps.MRP_ASSIGNMENT_SETS MSS,
apps.MRP_SR_ASSIGNMENTS_v MSA,
apps.PO_VENDORS PV,
apps.PO_VENDOR_SITES_ALL pvs,
apps.mtl_system_items_b msib
where msr.SOURCING_RULE_ID = msro.SOURCING_RULE_ID
and msro.SR_RECEIPT_ID = msso.SR_RECEIPT_ID
and msso.VENDOR_ID = pv.VENDOR_ID
and mss.ASSIGNMENT_SET_ID = msa.ASSIGNMENT_SET_ID
and MSA.SOURCING_RULE_ID = msr.SOURCING_RULE_ID
and msso.VENDOR_SITE_ID = pvs.VENDOR_SITE_ID
and msib.INVENTORY_ITEM_ID = msA.INVENTORY_ITEM_ID
and msib.SEGMENT2 = p_inv_item1
and pv.vendor_name = p_vendor
;
Cursor C_BPA(p_po_number varchar2
,p_line_num NUMBER
)
is
Select Distinct pha.SEGMENT1 as PO_NUMBER
,pv.vendor_name
,pvs.vendor_site_code
,pha.START_DATE
,pha.END_DATE
,(SELECT nvl(SUM((quantity-quantity_cancelled) * price_override), 0)
FROM po_line_locations_all
WHERE po_header_id = pha.po_header_id ) as BLANKET_RELEASE_TOTAL
,pha.AMOUNT_LIMIT
,pla.LINE_NUM
,msib.segment2 as INV_ITEM
,SUBSTR(pla.ITEM_DESCRIPTION,1,50) as ITEM_DESCRIPTION
,pla.UNIT_MEAS_LOOKUP_CODE
,pla.UNIT_PRICE
,MCB.SEGMENT2||'.'||MCB.SEGMENT4||'.'||MCB.SEGMENT3 as ITEM_CATEGORY
from apps.PO_HEADERS_ALL PHA,
apps.PO_LINES_ALL PLA,
apps.PO_VENDORS PV,
apps.PO_VENDOR_SITES_ALL pvs,
apps.mtl_system_items_b msib,
apps.MTL_CATEGORIES_B mcb
WHERE pha.SEGMENT1 = p_po_number
and pla.line_num = p_line_num
and pha.org_id = p_org_id
and PHA.PO_HEADER_ID = PLA.PO_HEADER_ID
and pla.org_id = pha.org_id(+)
and Msib.INVENTORY_ITEM_ID (+) = Pla.item_id
and pv.vendor_id (+) = pha.vendor_id
and pvs.vendor_site_id (+) = pha.vendor_site_id
and mcb.CATEGORY_ID = pla.CATEGORY_ID
and pha.TYPE_LOOKUP_CODE = 'BLANKET'
;
Begin
DBMS_APPLICATION_INFO.SET_CLIENT_INFO(p_org_id);
BEGIN
Select nvl(prh.Requisition_header_id,0)
INTO p_Requisition_header_id
From apps.po_requisition_headers_all Prh
where prh.SEGMENT1 = p_pr_no
and prh.org_id = p_org_id
;
IF p_Requisition_header_id !=0
THEN
dbms_output.put_line('!!!-------------------------------------------------------------------------');
dbms_output.put_line('!!! PO Requisition details ');
dbms_output.put_line('!!!-------------------------------------------------------------------------');
FOR c_rec in c_pr_cursor(p_Requisition_header_id)
loop
dbms_output.put_line('Requisition header id'||'!'||'Segment1'||'!'||'Authorization Status'||'!'||'Interface Source Code'||'!'||'Line Num'||'!'||'Inventory Item'||'!'||'Item Description'||'!'||'Item Category'||'!'||'Unit Meas Lookup Code'||'!'||'Need By Date'||'!'||'Vendor Name'||'!'||'Vendor Site Code'||'!'||'Po Number'||'!'|| 'Po Line Num'||'!'||'Organization Name'||'!'|| 'Org Id');
dbms_output.put_line(c_rec.Requisition_header_id
||'!'||c_rec.SEGMENT1
||'!'||c_rec.AUTHORIZATION_STATUS
||'!'||c_rec.interface_source_code
||'!'||c_rec.line_num
||'!'||c_rec.INV_ITEM
||'!'||c_rec.ITEM_DESCRIPTION
||'!'||c_rec.ITEM_CATEGORY
||'!'||c_rec.UNIT_MEAS_LOOKUP_CODE
||'!'||c_rec.need_by_date
||'!'||c_rec.vendor_name
||'!'||c_rec.vendor_site_code
||'!'||c_rec.PO_NUMBER
||'!'||c_rec.PO_LINE_NUM
||'!'||c_rec.ORGANIZATION_NAME
||'!'||c_rec.Org_Id);
dbms_output.put_line('!!------------------------------------');
dbms_output.put_line('!!Action History Details');
dbms_output.put_line('!!------------------------------------');
dbms_output.put_line('Object Type Code'||'!'||'Object Sub Type Code'||'!'||'Action Code'||'!'||'Employee Name');
FOR F_History in C_History (c_rec.Requisition_header_id)
Loop
dbms_output.put_line(F_History.OBJECT_TYPE_CODE
||'!'|| F_History.OBJECT_SUB_TYPE_CODE
||'!'|| F_History.ACTION_CODE
||'!'|| F_History.EMPLOYEE_NAME );
end loop;
dbms_output.put_line('!!------------------------------------');
dbms_output.put_line('!!Approval Supplier List');
dbms_output.put_line('!!------------------------------------');
dbms_output.put_line('Asl ID'||'!'||'Vendor Name'||'!'||'Vendor Site Code'
||'!'||'Inventory Item'||'!'||'Document Type Code'||'!'||'PO Number'
||'!'||'Start Date'||'!'||'End Date'||'!'||'Line Num');
FOR F_App_SL in C_App_SL (c_rec.INV_ITEM)
LOOP
dbms_output.put_line(F_App_SL.ASL_ID
||'!'||F_App_SL.VENDOR_NAME
||'!'||F_App_SL.VENDOR_SITE_CODE
||'!'||F_App_SL.INVENTORY_ITEM
||'!'||F_App_SL.DOCUMENT_TYPE_CODE
||'!'||F_App_SL.PO_NUMBER
||'!'||F_App_SL.START_DATE
||'!'||F_App_SL.END_DATE
||'!'||F_App_SL.LINE_NUM);
dbms_output.put_line('!!!-----------------------------------------------------------');
dbms_output.put_line('!!!Sourcing Rules and Assign Sourcing Rules');
dbms_output.put_line('!!!-----------------------------------------------------------');
dbms_output.put_line('Sourcing Rule Name'||'!'||'Description'||'!'||'Status'||'!'||'Planning Active'
||'!'||'Effective Date'||'!'||'Vendor Name'||'!'||'Vendor Site Code'
||'!'||'Allocation Percent'||'!'||'Rank1'||'!'||'Assignment Set Name'
||'!'||'Sourcing Rule Type'||'!'||'Sourcing Rule Type Text'||'!'||'Assignment Type'||'!'|| 'Inventory Item'
||'!'||'Assigned To');
FOR F_SR_ASR in C_SR_ASR (F_App_SL.VENDOR_NAME
,c_rec.INV_ITEM)
LOOP
dbms_output.put_line(F_SR_ASR.SOURCING_RULE_NAME
||'!'|| F_SR_ASR.DESCRIPTION
||'!'|| F_SR_ASR.STATUS
||'!'|| F_SR_ASR.PLANNING_ACTIVE
||'!'|| F_SR_ASR.EFFECTIVE_DATE
||'!'|| F_SR_ASR.VENDOR_NAME
||'!'|| F_SR_ASR.VENDOR_SITE_CODE
||'!'|| F_SR_ASR.ALLOCATION_PERCENT
||'!'|| F_SR_ASR.RANK1
||'!'|| F_SR_ASR.ASSIGNMENT_SET_NAME
||'!'|| F_SR_ASR.SOURCING_RULE_TYPE
||'!'|| F_SR_ASR.SOURCING_RULE_TYPE_TEXT
||'!'|| F_SR_ASR.ASSIGNMENT_TYPE
||'!'|| F_SR_ASR.INV_ITEM
||'!'|| F_SR_ASR.ASSIGNED_TO
);
END LOOP;
dbms_output.put_line('!!!---------------------------------------------');
dbms_output.put_line('!!!PO Header and Line Details');
dbms_output.put_line('!!!---------------------------------------------');
dbms_output.put_line('Po Number'||'!'||'Vendor Name'||'!'||'Vendor Site Code'||'!'||'Start Date'
||'!'||'End Date'||'!'||'Blanket Release Total'||'!'||'Amount Limit'
||'!'||'Line Num'||'!'||'Inventory Item'||'!'||'Item Description'||'!'||'Unit Meas Lookup Code'
||'!'||'Unit Price'||'!'||'Item Category');
FOR F_BPA in C_BPA(F_App_SL.PO_NUMBER
,F_App_SL.LINE_NUM)
LOOP
dbms_output.put_line(F_BPA.PO_NUMBER
||'!'||F_BPA.VENDOR_NAME
||'!'|| F_BPA.VENDOR_SITE_CODE
||'!'|| F_BPA.START_DATE
||'!'|| F_BPA.END_DATE
||'!'|| F_BPA.BLANKET_RELEASE_TOTAL
||'!'|| F_BPA.AMOUNT_LIMIT
||'!'|| F_BPA.LINE_NUM
||'!'|| F_BPA.INV_ITEM
||'!'|| F_BPA.ITEM_DESCRIPTION
||'!'|| F_BPA.UNIT_MEAS_LOOKUP_CODE
||'!'|| F_BPA.UNIT_PRICE
||'!'|| F_BPA.ITEM_CATEGORY
);
END LOOP;
END LOOP;
end loop;
Else
Null;
END IF;
Exception
WHEN NO_DATA_FOUND THEN
v_ora_err_msg_desc := sqlerrm;
v_ora_err_msg_code := to_char(sqlcode);
v_err_msg := 'XXSRIERR: No Data Found ';
DBMS_OUTPUT.PUT_LINE(v_err_msg);
DBMS_OUTPUT.PUT_LINE('XXSRIERR: Oracle error is: ' || v_ora_err_msg_code);
DBMS_OUTPUT.PUT_LINE('v_ora_err_msg_desc : ' ||v_ora_err_msg_desc);
--RAISE vld_error;
WHEN OTHERS THEN
v_ora_err_msg_desc := sqlerrm;
v_ora_err_msg_code := to_char(sqlcode);
v_err_msg := 'XXSRIERR: SQL error while selecting PR Information.';
DBMS_OUTPUT.PUT_LINE(v_err_msg);
DBMS_OUTPUT.PUT_LINE('XXSRIERR: Oracle error is: ' || v_ora_err_msg_code);
DBMS_OUTPUT.PUT_LINE(v_ora_err_msg_desc);
RAISE vld_error;
END;
END;
/
spool off;
0 comments:
Post a Comment