Overview:
A blanket
release is an actual order of goods or services you raise against a blanket
purchase agreement. The blanket purchase agreement determines the
characteristics and the prices of the items. The release can be created
manually or automatically.
This article help you to create or upload Blanket Releases from one instance to another instance.
I have used this article to upload Blanket Releases from 11i instance to R12.2.7
Use below steps for complete your Interface or Conversion of Blanket Releases from Source instance to Destination instance.
1. Insert data in staging table.
Insert data in staging table using PL/SQL block or manually.
Use below data extract query to get data from Source instance:
SELECT pha.segment1
po_number,
pla.line_num,
msib.segment1 item_number,
msib.description item_description,
(plla.quantity - (plla.quantity_received +
plla.quantity_cancelled)) quantity,
pla.unit_price base_unit_price,
hr.location_code deliver_to_location_code,
pha.type_lookup_code document_type_code,
pr.creation_date,
papf.full_name deliver_to_requestor_name,
ood.organization_code
destination_organization_code,
plla.need_by_date,
msib.primary_uom_code supplier_uom,
msib.primary_uom_code supplier_uom,
papf.full_name preparer_name,
pr.po_release_id batch_id, -- Or use any sequence
for BATCH_ID
pha.org_id,
plla.shipment_num,
pr.release_num,
pr.release_date,
glcc.segment1||'.'||glcc.segment2||'.'||glcc.segment3||'.'||glcc.segment4||'.'||glcc.segment5||'.'||glcc.segment6||'.'||glcc.segment7
charge_account
FROM mtl_system_items_b msib
, po_lines_all pla
, po_line_locations_all plla
, po_headers_all pha
, hr_locations hr
, org_organization_definitions ood
, po_releases_all pr
, per_all_people_f papf
, po_distributions_all pod
, gl_code_combinations glcc
WHERE pla.item_id = msib.inventory_item_id
AND
pla.po_line_id =
plla.po_line_id
AND
pha.po_header_id =
pla.po_header_id
AND pha.ship_to_location_id = hr.location_id
AND hr.inventory_organization_id = msib.organization_id
AND hr.inventory_organization_id = msib.organization_id
AND
pha.po_header_id =
pr.po_header_id
AND
plla.po_release_id =
pr.po_release_id
AND
pha.agent_id =
papf.person_id
AND
pha.org_id =
pla.org_id(+)
AND pla.po_line_id = pod.po_line_id
AND
plla.po_header_id =
pod.po_header_id
AND
pod.code_combination_id =
glcc.code_combination_id(+)
AND
plla.line_location_id =
pod.line_location_id
AND
pha.type_lookup_code = 'BLANKET'
AND
pha.authorization_status = 'APPROVED'
AND
NVL(msib.outside_operation_flag,'N') !='Y'
AND
msib.organization_id =
ood.organization_id
AND
pha.agent_id = papf.person_id
AND
pha.org_id = pla.org_id
AND
pha.org_id = 204 -- Operating Unit wise data
AND
plla.po_release_id IS NOT NULL
AND NVL
(pla.closed_code, 'OPEN') IN ('OPEN')
AND NVL
(plla.closed_code, 'OPEN') IN ('OPEN')
AND NVL
(pha.closed_code, 'OPEN') IN ('OPEN')
AND NVL
(pr.closed_code, 'OPEN') IN ('OPEN')
AND
(plla.quantity - (plla.quantity_received + plla.quantity_cancelled)) > 0;
For Example:
**Package Specification
-------------------------------
CREATE OR REPLACE PACKAGE xxpo_bpo_releases_pkg
AS
PROCEDURE XX_main_prc (
p_errbuf OUT VARCHAR2,
p_retcode OUT VARCHAR2,
p_organization_id IN NUMBER
);
END xxpo_bpo_releases_pkg;
/
**Package Body
--------------------------------
CREATE OR REPLACE PACKAGE BODY XX_bpo_releases_pkg
AS
PROCEDURE xx_main_prc (
p_errbuf OUT VARCHAR2,
p_retcode OUT VARCHAR2,
p_organization_id IN NUMBER
)
IS
-- This cursor will fetch the data from xx_bpo_release_stg table for which process_flag is NULL.
CURSOR lcur_po_bpo_releases (p_organization_id IN NUMBER)
IS
SELECT po_number, item_description, quantity, base_unit_price,
deliver_to_location_code, document_type_code, line_num,
creation_date, deliver_to_requestor_name,
destination_organization_code, item_segment1, need_by_date,
preparer_name, requisition_type,
source_type_code, org_id, shipment_num, release_num,
release_date, supplier_uom, deliver_to_location_id,
deliver_to_person_id
FROM xx_bpo_release_stg
WHERE process_flag IS NULL
AND custom_error_message IS NULL
AND org_id = p_organization_id
GROUP BY po_number,
item_description,
quantity,
base_unit_price,
deliver_to_location_code,
document_type_code,
line_num,
creation_date,
deliver_to_requestor_name,
destination_organization_code,
item_segment1,
need_by_date,
preparer_name,
requisition_type,
**Interface Table:
PO_REQUISITIONS_INTERFACE_ALL2. Validate staging table data, create Package/Procedure for validating data:
For Example:
**Package Specification
-------------------------------
CREATE OR REPLACE PACKAGE xxpo_bpo_releases_pkg
AS
PROCEDURE XX_main_prc (
p_errbuf OUT VARCHAR2,
p_retcode OUT VARCHAR2,
p_organization_id IN NUMBER
);
END xxpo_bpo_releases_pkg;
/
**Package Body
--------------------------------
CREATE OR REPLACE PACKAGE BODY XX_bpo_releases_pkg
AS
PROCEDURE xx_main_prc (
p_errbuf OUT VARCHAR2,
p_retcode OUT VARCHAR2,
p_organization_id IN NUMBER
)
IS
-- This cursor will fetch the data from xx_bpo_release_stg table for which process_flag is NULL.
CURSOR lcur_po_bpo_releases (p_organization_id IN NUMBER)
IS
SELECT po_number, item_description, quantity, base_unit_price,
deliver_to_location_code, document_type_code, line_num,
creation_date, deliver_to_requestor_name,
destination_organization_code, item_segment1, need_by_date,
preparer_name, requisition_type,
source_type_code, org_id, shipment_num, release_num,
release_date, supplier_uom, deliver_to_location_id,
deliver_to_person_id
FROM xx_bpo_release_stg
WHERE process_flag IS NULL
AND custom_error_message IS NULL
AND org_id = p_organization_id
GROUP BY po_number,
item_description,
quantity,
base_unit_price,
deliver_to_location_code,
document_type_code,
line_num,
creation_date,
deliver_to_requestor_name,
destination_organization_code,
item_segment1,
need_by_date,
preparer_name,
requisition_type,
source_type_code,
org_id,
shipment_num,
release_num,
release_date,
supplier_uom,
deliver_to_location_id,
deliver_to_person_id;
org_id,
shipment_num,
release_num,
release_date,
supplier_uom,
deliver_to_location_id,
deliver_to_person_id;
-- Declare Variables
-- Header Level
ln_user_id NUMBER := fnd_global.user_id;
lc_rec_status VARCHAR2 (10);
lc_error_msg CLOB;
--
ln_deliver_to_location_code NUMBER;
ln_org_id2 NUMBER := p_organization_id;
ln_headers_cnt NUMBER := 0;
lc_deliver_to_requestor_name VARCHAR2 (240);
lc_preparer_name VARCHAR2 (240);
lc_item_segment1 VARCHAR2 (80);
ln_organization_id NUMBER;
ln_inventory_item_id NUMBER;
ln_item_id NUMBER;
lc_release_num VARCHAR2 (20);
lc_po_number VARCHAR2 (30);
ln_po_header_id NUMBER;
ln_po_line_id NUMBER;
ln_charge_account_id NUMBER;
ln_deliver_to_location_id NUMBER;
ln_deliver_to_person_id NUMBER;
lc_deliver_to_location VARCHAR2 (250);
lc_deliver_to_person VARCHAR2 (250);
ln_vendor_id NUMBER;
ln_vendor_site_id NUMBER;
--
BEGIN
-- Start PO Header loop
FOR lrec_release IN lcur_po_bpo_releases (ln_org_id2)
LOOP
-- Assign values to header level variables
lc_rec_status := 'A';
lc_error_msg := NULL;
ln_deliver_to_location_code := 0;
lc_deliver_to_requestor_name := NULL;
lc_preparer_name := NULL;
lc_item_segment1 := NULL;
ln_organization_id := 0;
ln_inventory_item_id := 0;
ln_item_id := 0;
lc_release_num := NULL;
lc_po_number := NULL;
ln_po_header_id := 0;
ln_po_line_id := 0;
ln_charge_account_id := NULL;
ln_deliver_to_location_id := 0;
ln_deliver_to_person_id := 0;
lc_deliver_to_location := NULL;
lc_deliver_to_person := NULL;
ln_vendor_id := NULL;
ln_vendor_site_id := NULL;
-- Start Header level validation
--
--Validation of PO NUMBER
IF (lrec_release.po_number IS NOT NULL) THEN
BEGIN
SELECT segment1, po_header_id, vendor_id,
vendor_site_id
INTO lc_po_number, ln_po_header_id, ln_vendor_id,
ln_vendor_site_id
FROM po_headers_all
WHERE UPPER (segment1) = UPPER (lrec_release.po_number)
AND type_lookup_code = 'BLANKET'
AND (end_date_active IS NULL OR end_date_active > SYSDATE)
AND enabled_flag = 'Y'
AND authorization_status = 'APPROVED'
AND NVL (closed_code, 'OPEN') = 'OPEN';
EXCEPTION
WHEN TOO_MANY_ROWS THEN
fnd_file.put_line (fnd_file.LOG, 'Get more than one record of PO Number:' || lrec_release.po_number);
lc_rec_status := 'E';
WHEN NO_DATA_FOUND THEN
fnd_file.put_line (fnd_file.LOG,'PO Number:' || lrec_release.po_number || ' not found.'||SQLERRM);
lc_rec_status := 'E';
END;
ELSE
fnd_file.put_line (fnd_file.LOG, ' PO Number is empty.');
lc_rec_status := 'E';
END IF;
-- Get po_line_id
BEGIN
SELECT po_line_id
INTO ln_po_line_id
FROM po_lines_all
WHERE po_header_id = ln_po_header_id
AND line_num = lrec_release.line_num;
EXCEPTION
WHEN NO_DATA_FOUND THEN
fnd_file.put_line (fnd_file.LOG, 'PO line no.:' || lrec_release.line_num || ' not found.' );
lc_rec_status := 'E';
WHEN OTHERS THEN
fnd_file.put_line (fnd_file.LOG, 'Error in PO line no.:' || lrec_release.line_num || SQLERRM);
lc_rec_status := 'E';
END;
-- Validation for deliver_to_location_code
IF (lrec_release.deliver_to_location_code IS NOT NULL) THEN
BEGIN
SELECT location_id
INTO ln_deliver_to_location_code
FROM hr_locations_all
WHERE UPPER (location_code) = UPPER (lrec_release.deliver_to_location_code);
EXCEPTION
WHEN TOO_MANY_ROWS THEN
fnd_file.put_line (fnd_file.LOG, 'Get more than one record of Deliver to location code:' || lrec_release.deliver_to_location_code);
lc_rec_status := 'E';
WHEN NO_DATA_FOUND THEN
fnd_file.put_line (fnd_file.LOG, 'Deliver to location code:' || lrec_release.deliver_to_location_code|| ' not found.' || SQLERRM );
lc_rec_status := 'E';
END;
ELSE
fnd_file.put_line (fnd_file.LOG, ' Deliver to location code is empty.' );
lc_rec_status := 'E';
END IF;
-- Validation of Charge_account
IF (lrec_release.charge_account IS NOT NULL) THEN
BEGIN
SELECT glcc.code_combination_id
INTO ln_charge_account_id
FROM gl_code_combinations glcc
WHERE glcc.segment1|| '.'|| glcc.segment2|| '.'|| glcc.segment3|| '.'|| glcc.segment4|| '.'|| glcc.segment5|| '.'
|| glcc.segment6|| '.'|| glcc.segment7 = lrec_release.gl_code AND (end_date_active IS NULL OR end_date_active > SYSDATE);
EXCEPTION
WHEN TOO_MANY_ROWS THEN
fnd_file.put_line
(fnd_file.LOG, ' Too many rows found against charge_account: ' || lrec_release.charge_account);
lc_rec_status := 'E';
WHEN OTHERS THEN
fnd_file.put_line (fnd_file.LOG,' Error in charge_account: '|| lrec_release.charge_account || ', error: ' || SQLERRM);
lc_rec_status := 'E';
END;
END IF;
-- Validation for deliver_to_requestor_name
IF (lrec_release.deliver_to_requestor_name IS NOT NULL) THEN
BEGIN
SELECT full_name
INTO lc_deliver_to_requestor_name
FROM per_all_people_f
WHERE UPPER (full_name) =
UPPER (lrec_release.deliver_to_requestor_name);
EXCEPTION
WHEN TOO_MANY_ROWS THEN
fnd_file.put_line (fnd_file.LOG, 'Get more than one record of deliver to requestor name:' || lrec_release.deliver_to_requestor_name);
lc_rec_status := 'E';
WHEN NO_DATA_FOUND THEN
fnd_file.put_line (fnd_file.LOG,'Deliver to requestor name:'|| lrec_release.deliver_to_requestor_name|| ' not found.'|| SQLERRM);
lc_rec_status := 'E';
END;
ELSE
fnd_file.put_line(fnd_file.LOG,' Deliver to requestor name is empty.');
lc_rec_status := 'E';
END IF;
-- Validation for preparer_name
IF (lrec_release.preparer_name IS NOT NULL) THEN
BEGIN
SELECT full_name
INTO lc_preparer_name
FROM per_all_people_f
WHERE UPPER (full_name) = UPPER (lrec_release.preparer_name);
EXCEPTION
WHEN TOO_MANY_ROWS THEN
fnd_file.put_line (fnd_file.LOG, 'Get more than one record of preparer name:'|| lrec_release.preparer_name);
lc_rec_status := 'E';
WHEN NO_DATA_FOUND THEN
fnd_file.put_line (fnd_file.LOG,' Preparer name:'|| lrec_release.preparer_name|| ' not found.'|| SQLERRM);
lc_rec_status := 'E';
END;
ELSE
fnd_file.put_line (fnd_file.LOG, ' Preparer name is empty.');
lc_rec_status := 'E';
lc_error_msg := lc_error_msg || ' Preparer name is empty.';
END IF;
-- Get organization_id
IF (lrec_release.destination_organization_code IS NOT NULL) THEN
BEGIN
SELECT organization_id
INTO ln_organization_id
FROM org_organization_definitions
WHERE UPPER (organization_code) = UPPER (lrec_release.destination_organization_code);
EXCEPTION
WHEN TOO_MANY_ROWS THEN
fnd_file.put_line(fnd_file.LOG,
'Get more than one record of distination organization_code:'|| lrec_release.destination_organization_code);
lc_rec_status := 'E';
WHEN NO_DATA_FOUND THEN
fnd_file.put_line(fnd_file.LOG, ' Organization code:'|| lrec_release.destination_organization_code|| ' not found.'|| SQLERRM);
lc_rec_status := 'E';
END;
ELSE
fnd_file.put_line (fnd_file.LOG, ' Organization code is empty.');
lc_rec_status := 'E';
END IF;
-- Validation for item_number
IF (lrec_release.item_segment1 IS NOT NULL) THEN
BEGIN
SELECT segment1, inventory_item_id
INTO lc_item_segment1, ln_inventory_item_id
FROM mtl_system_items_b
WHERE UPPER (segment1) = UPPER (lrec_release.item_segment1)
AND organization_id = ln_organization_id;
EXCEPTION
WHEN TOO_MANY_ROWS THEN
fnd_file.put_line (fnd_file.LOG, 'Get more than one record of item:'|| lrec_release.item_segment1);
lc_rec_status := 'E';
WHEN NO_DATA_FOUND THEN
lc_rec_status := 'E';
END;
ELSE
fnd_file.put_line (fnd_file.LOG, ' Item segment1 is empty.');
lc_rec_status := 'E';
END IF;
-- Check Item is assign or not to ASL, SR and assignment
-- This is important, without ASL, SR and assignment Blanket Release not created.
BEGIN
SELECT DISTINCT pasl.item_id
INTO ln_item_id
FROM po_approved_supplier_list pasl,
mrp_sr_assignments msa,
mrp_sr_receipt_org_v msr,
mrp_sr_source_org_v msrv,
po_asl_attributes_v paav
WHERE 1 = 1
AND pasl.item_id = msa.inventory_item_id
AND pasl.owning_organization_id = msa.organization_id
AND msa.sourcing_rule_id = msr.sourcing_rule_id
AND msr.sr_receipt_id = msrv.sr_receipt_id
AND msrv.vendor_id = pasl.vendor_id
AND msrv.vendor_site_id = pasl.vendor_site_id
AND paav.asl_id = pasl.asl_id
AND organization_id = ln_organization_id
AND inventory_item_id = ln_inventory_item_id
AND pasl.vendor_id = ln_vendor_id
AND pasl.vendor_site_id = ln_vendor_site_id
AND NVL (pasl.disable_flag, 'N') = 'N'
AND UPPER (paav.release_generation_method_dsp) = 'AUTOMATIC RELEASE'
AND ( msr.disable_date IS NULL
OR msr.disable_date > SYSDATE
);
EXCEPTION
WHEN TOO_MANY_ROWS THEN
fnd_file.put_line(fnd_file.LOG,'Get more than one record of ASL for item:'|| lrec_release.item_number);
lc_rec_status := 'E';
WHEN NO_DATA_FOUND THEN
fnd_file.put_line(fnd_file.LOG,' ASL/SR/Assignment is missing for Item:'|| lrec_release.item_number);
lc_rec_status := 'E';
END;
-- Get deliver_to_location_code
IF (lrec_release.deliver_to_location_code IS NOT NULL)
THEN
BEGIN
SELECT location_id, location_code
INTO ln_deliver_to_location_id, lc_deliver_to_location
FROM hr_locations_all
WHERE (inactive_date IS NULL OR inactive_date > SYSDATE)
AND location_code = lrec_release.deliver_to_location_code;
EXCEPTION
WHEN TOO_MANY_ROWS THEN
lc_rec_status := 'E';
WHEN OTHERS THEN
lc_rec_status := 'E';
END;
END IF;
-- Get deliver_to_person_id
IF (lrec_release.deliver_to_requestor_name IS NOT NULL)
THEN
BEGIN
SELECT person_id, full_name
INTO ln_deliver_to_person_id, lc_deliver_to_person
FROM per_all_people_f
WHERE ( effective_end_date IS NULL OR effective_end_date > SYSDATE)
AND full_name = lrec_release.deliver_to_requestor_name;
EXCEPTION
WHEN too_many_rows THEN
lc_rec_status := 'E';
WHEN OTHERS THEN
fnd_file.put_line (fnd_file.LOG, ' Error in deliver_to_requestor_name :' || SQLERRM );
lc_rec_status := 'E';
END;
END IF;
-- Insert valid records in po_requisitions_interface_all table.
IF (lc_rec_status = 'A')
THEN
BEGIN
INSERT INTO po_requisitions_interface_all
(interface_source_code,
source_type_code,
destination_type_code,
authorization_status,
preparer_name,
item_segment1,
quantity,
charge_account_id,
uom_code,
destination_organization_code,
deliver_to_location_code,
autosource_flag,
need_by_date,
deliver_to_requestor_name,
org_id,
autosource_doc_header_id,
autosource_doc_line_num,
created_by,
creation_date,
last_updated_by,
last_update_date, last_update_login,
line_attribute14, suggested_vendor_id,
suggested_vendor_site_id
)
VALUES ('XX_007', -- interface_source_code
'VENDOR', -- source_type_code
'INVENTORY', -- destination_type_code
'APPROVED', -- authorization_status
lrec_release.preparer_name, -- preparer_name
lrec_release.item_number, -- item_segment1
lrec_release.quantity, -- quantity
ln_charge_account_id, -- charge_account_id
lrec_release.supplier_uom, -- uom_code
lrec_release.destination_organization_code, -- destination_organization_code
NVL (lc_deliver_to_location,lrec_release.deliver_to_location_code), -- deliver_to_location_code
'Y', -- autosource_flag
lrec_release.need_by_date, -- need_by_date
NVL (lc_deliver_to_person,
lrec_release.preparer_name
), -- deliver_to_requestor_name
lrec_release.org_id, -- org_id
ln_po_header_id, -- autosource_doc_header_id
ln_po_line_id, -- autosource_doc_line_num
fnd_global.user_id, -- created_by
SYSDATE, -- creation_date
fnd_global.user_id, -- last_updated_by
SYSDATE, -- last_update_date
fnd_global.user_id, -- last_update_login
lrec_release.req_number_segment1,-- line_attribute14
ln_vendor_id, -- suggested_vendor_id
ln_vendor_site_id -- suggested_vendor_site_id
);
EXCEPTION
WHEN OTHERS THEN
fnd_file.put_line (fnd_file.LOG, 'Error in insert data in po_requisitions_interface_all table, Error:' || SQLERRM );
lc_rec_status := 'E';
lc_error_msg := lc_error_msg || ' Error in insert data in po_requisitions_interface_all table, Error:' || SQLERRM;
END;
END IF;
-- Update process_flag in staging table
UPDATE xx_bpo_release_stg
SET process_flag = lc_rec_status
WHERE 1 = 1
AND po_number = lrec_release.po_number
AND line_num = lrec_release.line_num
AND release_num = lrec_release.release_num
AND org_id = lrec_release.org_id
AND shipment_num = lrec_release.shipment_num
AND need_by_date = lrec_release.need_by_date;
--
IF (lc_rec_status = 'A')
THEN
ln_headers_cnt := ln_headers_cnt + 1;
END IF;
--
END LOOP; -- End of Loop..
fnd_file.put_line (fnd_file.LOG, 'Total successfully inserted POs are:'|| ln_headers_cnt );
--
END xx_main_prc;
END xx_bpo_releases_pkg;
/ ------------------------------------------------------------------------------
3. Run standard program :
Standard Program Name: Requisition Import (enter mandatory parameters).
4. Data inserted/updated in below base tables:
1) PO_REQUISITION_HEADERS_ALL
2) PO_REQUISITION_LINES_ALL
3) PO_REQ_DISTRIBUTIONS_ALL
4) PO_LINE_LOCATIONS_ALL (po_release_id available)
5) PO_RELEASES_ALL (po_header_id available)
6) PO_DISTRIBUTIONS_ALL
7) MTL_SUPPLY
*** Below are some SQL queries related to Blanket Releases:
Check Sourcing Rule available or not for Item:
SELECT * FROM mrp_sourcing_rules WHERE
sourcing_rule_name = 'A007123'; -- this is Item Number.
SELECT * FROM mrp_sr_receipt_org_v WHERE
sourcing_rule_id = 5124082;
SELECT * FROM mrp_sr_source_org_v WHERE
sr_receipt_id = 5861265;
-- Check Item Sourcing Rule allocation percentage - Vendor & Vendor Site wise:
SELECT msr.sourcing_rule_name
,
(SELECT vendor_name FROM ap_suppliers WHERE vendor_id = pha.vendor_id)
||'-'|| (SELECT vendor_site_code FROM ap_supplier_sites_all WHERE
vendor_site_id = pha.vendor_site_id) po_vendor_name
, source.vendor_name ||'-'|| source.vendor_site
vendor_name
, source.allocation_percent
, source.source_type
, msr.status
, msr.sourcing_rule_type
, msr.planning_active
, receipt.organization_code
FROM mrp_sourcing_rules msr
,mrp_sr_receipt_org_v receipt
,mrp_sr_source_org_v source
,po_headers_all pha
,po_lines_all pla
,mtl_system_items_b msib
,org_organization_definitions ood
WHERE msr.sourcing_rule_id =
receipt.sourcing_rule_id
AND receipt.sr_receipt_id = source.sr_receipt_id
AND pla.po_header_id = pha.po_header_id
AND
pla.item_id = msib.inventory_item_id
AND
msib.segment1 = msr.sourcing_rule_name
AND
msib.organization_id = receipt.receipt_organization_id
AND
msib.organization_id = ood.organization_id
AND
pha.segment1 = '200776'
AND
msr.sourcing_rule_name = 'A007123' -- this is Item Number
AND
ood.organization_code = 'KUI' -- this is Organization Code
ORDER BY
1,2;
-- Query to check Blanket Releases created or not against Blanket:
SELECT pha.segment1 po_number
, pra.release_num
FROM po_releases_all pra
, po_headers_all pha
WHERE pra.po_header_id = pha.po_header_id
AND pha.segment1 = '200776';
-- '200776' this is Blanket Number.
