2011年3月29日星期二

API to import ASLs (approved supplier list) in Oracle from blanket agreements or quotations // Import des ASLs à partir des commandes ouverts ou prix

Since Nov 2008(patch ID 262093.1), Oracle provides the possibility to create local Approved Supplier List (ASL) through the PO Open Interface via the concurrent program "Generate Sourcing Rules and ASLs from Blanket Agreement".

With this concurrent program, we can create the ASLs from the blankets already imported into Oracle PO. It's useful in the case of we use blanket agreements. If we don't use blanket but only use quotation, it's impossible for us to mass create ASLs.

So we duplicated the standard concurrent program "Generate Sourcing Rules and ASLs from Blanket Agreement" and created another new specific concurrent program "XX : Generate Sourcing Rules and ASLs from Quotations".

The executable file is always the same : POXSRASL.sql
This program requires 12 parameters that 5 need to be modified :

Parameter 1 - Supplier
LOV 1 - PO_ASL_VENDOR

Parameter 2 - Blanket Agreement Number
LOV 2 - SE_PO_ASL_BLANKET_NUMBER
Define LOV -
Table : PO_HEADERS_ALL POH,PO_LOOKUP_CODES PLC
Valeur : POH.segment1
ID : POH.po_header_id
WHERE : POH.vendor_id=:$FLEX$.PO_ASL_VENDOR
and NVL(POH.closed_code,'OPEN') not in ('CLOSED','FINALLY CLOSED')
and POH.type_lookup_code='QUOTATION'
and
(
poh.org_id IN (SELECT ORG_ID FROM PO_SYSTEM_PARAMETERS)
OR
(
POH.global_agreement_flag='Y' and
EXISTS (
select pgoa.purchasing_org_id
from po_ga_org_assignments pgoa
where pgoa.po_header_id=poh.po_header_id and
pgoa.purchasing_org_id
IN (SELECT ORG_ID FROM PO_SYSTEM_PARAMETERS)
)
)
)
AND PLC.LOOKUP_TYPE='YES/NO'
AND PLC.LOOKUP_CODE=NVL(POH.GLOBAL_AGREEMENT_FLAG,'N')
ORDER BY : PLC.DISPLAYED_FIELD "Global Agreement"

Parameter 3 - Global Agreement Flag
LOV 3 - PO_ASL_ENABLE_FLAG
Default val - select decode(nvl(poh.global_agreement_flag,'N'),'N',null,'Y') from po_headers_all poh where poh.po_header_id=:$FLEX$.SE_PO_ASL_BLANKET_NUMBER

Parameter 4 - Purchasing Organization
LOV 4 - SE_PO_ASL_PURCHASING_ORG
Default val - SELECT name FROM hr_all_organization_units_tl houtl WHERE houtl.organization_id= decode(nvl(:$FLEX$.PO_ASL_ENABLE_FLAG:NULL,'N'),'Y',decode ( ( SELECT count(*) FROM po_ga_org_assignments pgoa WHERE pgoa.purchasing_org_id=:$PROFILES$.ORG_ID AND pgoa.po_header_id=:$FLEX$.SE_PO_ASL_BLANKET_NUMBER ) , 1, :$PROFILES$.ORG_ID, null ) ,null) AND language=USERENV('LANG')
Define LOV - Table : PO_HEADERS_ALL POH,PO_GA_ORG_ASSIGNMENTS PGOA, HR_ALL_ORGANIZATION_UNITS HOU,HR_ALL_ORGANIZATION_UNITS HOU1, HR_ALL_ORGANIZATION_UNITS HOU2
Where : POH.global_agreement_flag=:$FLEX$.PO_ASL_ENABLE_FLAG
and POH.po_header_id=:$FLEX$.SE_PO_ASL_BLANKET_NUMBER
and PGOA.po_header_id = POH.po_header_id
and ((POH.org_id IN (SELECT ORG_ID FROM PO_SYSTEM_PARAMETERS))
or (PGOA.purchasing_org_id IN (SELECT ORG_ID FROM PO_SYSTEM_PARAMETERS)))
and HOU.organization_id=PGOA.purchasing_org_id
and HOU1.organization_id=POH.org_id
and HOU2.organization_id=PGOA.organization_id
Order by : hou1.name as "Owning Organization",
hou2.name as "Requesting Organization"


Parameter 5 - Supplier Site
LOV 5 - SE_PO_ASL_VENDOR_SITE
Default val - SELECT pvs.vendor_site_code FROM po_vendor_sites pvs, po_headers_all poh WHERE pvs.vendor_site_id = decode( nvl(poh.global_agreement_flag,'N'),'N',poh.vendor_site_id, decode ( ( SELECT count(DISTINCT(pgoa.vendor_site_id)) FROM po_ga_org_assignments pgoa WHERE pgoa.purchasing_org_id=nvl(:$FLEX$.SE_PO_ASL_PURCHASING_ORG:NULL ,null) AND pgoa.po_header_id=:$FLEX$.SE_PO_ASL_BLANKET_NUMBER ) , 1, ( SELECT DISTINCT (pgoa.vendor_site_id) FROM po_ga_org_assignments pgoa WHERE pgoa.po_header_id=:$FLEX$.SE_PO_ASL_BLANKET_NUMBER AND pgoa.purchasing_org_id=nvl(:$FLEX$.SE_PO_ASL_PURCHASING_ORG:NULL ,null) ) , poh.vendor_site_id ) ) AND poh.po_header_id=:$FLEX$.SE_PO_ASL_BLANKET_NUMBER
Define LOV - Table : PO_VENDOR_SITES_ALL POV,PO_GA_ORG_ASSIGNMENTS PGOA,PO_HEADERS_ALL POH
Where : PGOA.po_header_id(+)=:$FLEX$.SE_PO_ASL_BLANKET_NUMBER
AND POH.po_header_id=:$FLEX$.SE_PO_ASL_BLANKET_NUMBER
and PGOA.purchasing_org_id(+)=
decode(nvl(POH.global_agreement_flag,'N'),'N',-999,
nvl(:$FLEX$.PO_ASL_PURCHASING_ORG:NULL,(:$PROFILES$.ORG_ID)))
and POV.vendor_site_id=decode(nvl(POH.global_agreement_flag,'N'),'N',
POH.vendor_site_id,
PGOA.vendor_site_id)

Parameter 6 - Select Agreement Lines
LOV 6 - PO_ASL_LINES_SELECTION

Parameter 7 - Assignment Set
LOV 7 - PO_ASL_ASSIGNMENT_SET
Default val - select assignment_set_name from mrp_assignment_sets where assignment_set_id=:$PROFILES$.MRP_DEFAULT_ASSIGNMENT_SET

Parameter 8 - Sourcing Level
LOV 8 - PO_ASL_SOURCING_LEVEL

Parameter 9 - Inv Org Enable
LOV 9 - PO_ASL_ENABLE_FLAG
Default val - select decode(:$FLEX$.PO_ASL_SOURCING_LEVEL,'ITEM',NULL,'Y') from dual

Parameter 10 - Inventory Organization
LOV 10 - PO_ASL_INV_ORG

Parameter 11 - Sourcing Rule Name
LOV 11 - SE_PO_ASL_SR_RULE_NAME
Define LOV - Table : MRP_SOURCING_RULES MSR , org_organization_definitions oog
Where : oog.organization_id = msr.organization_id
AND EXISTS
(
SELECT
1
FROM MRP_SR_RECEIPT_ORG MSRO,
MRP_SR_SOURCE_ORG MSSO
WHERE MSR.SOURCING_RULE_ID=MSRO.SOURCING_RULE_ID
AND MSRO.SR_RECEIPT_ID=msso.SR_RECEIPT_ID
AND msso.vendor_id=:$FLEX$.PO_ASL_VENDOR
AND msso.vendor_site_id=:$FLEX$.SE_PO_ASL_VENDOR_SITE
)
and oog.organization_id=:$FLEX$.PO_ASL_INV_ORG
Order By : oog.organization_name "Organization"


Parameter 12 - Release Generation Method
LOV 12 - PO_PDOI_REL_GEN_METHOD

This concurrent program allows users to create ASLs from one specific or more quotations. We can also customize this program by adding a new parameter request_id so that only quotations with this request_id (probably the request_id of program Import of price catalog) will be treated.

没有评论:

发表评论