Customer PO Conversion

Customer
PO Conversion


Description

            This blog is used to create a Customer PO conversion.
This blog will invoke the API OE_BLANKET_PUB.process_blanket
and it processes the header and line level information with validations.

— This query is used to retrieves the
customer account related information’s
SELECT   hca.account_number, hcasa.cust_account_id, hcasa.attribute4 custpo
FROM hz_cust_acct_sites_all hcasa,
hz_cust_accounts hca
WHERE 1 = 1
AND hca.cust_account_id = hcasa.cust_account_id
AND hcasa.attribute_category = ‘ARCUS’
AND hca.account_number = NVL (p_cust_acct_i, hca.account_number)
AND hcasa.attribute4 IS NOT NULL
AND EXISTS
    (
    SELECT DISTINCT purchase_order
    FROM ra_customer_trx_all rct,
   
ra_cust_trx_types_all rctta
    WHERE 1 = 1
    AND rct.cust_trx_type_id = rctta.cust_trx_type_id
    AND UPPER (LTRIM (RTRIM (rctta.description))) LIKE ‘%OSDP%INV%’
    AND rct.complete_flag = ‘Y’
    AND rct.purchase_order IS NOT NULL
    AND TRUNC (rct.trx_date) BETWEEN TO_DATE(’04-01-2015′,‘MM-DD-YYYY’)
    AND TO_DATE(’04-30-2015′,‘MM-DD-YYYY’)
    AND rct.purchase_order = hcasa.attribute4
    )
AND hcasa.status = ‘A’
AND hca.status = ‘A’
AND NOT EXISTS
    (
    SELECT cust_po_number
    FROM ont.oe_blanket_headers_all obha
    WHERE cust_po_number = hcasa.attribute4
    AND obha.sold_to_org_id = hcasa.cust_account_id
    )
GROUP BY
    hca.account_number, hcasa.cust_account_id, hcasa.attribute4;

— This query is used to
retrieves the customer and site related
   Information’s
SELECT hps.party_site_number, hp.party_name, hca.account_number,
       hca.status cust_account_status, hcasa.status cust_site_status,
       hcasa.attribute4 custpo, hcsua.site_use_code, hcsua.site_use_id
  FROM hz_parties hp,
      
hz_cust_accounts hca
,
      
hz_locations hl
,
      
hz_party_sites hps
,
      
hz_cust_acct_sites_all hcasa
,
      
hz_cust_site_uses_all hcsua
 WHERE 1 = 1
   AND hp.party_id = hca.party_id
   AND hp.party_id = hps.party_id
   AND hl.location_id = hps.location_id
   AND hca.cust_account_id = hcasa.cust_account_id
   AND hps.party_site_id = hcasa.party_site_id
   AND hcasa.cust_acct_site_id = hcsua.cust_acct_site_id
   AND hcsua.site_use_code = ‘SHIP_TO’
   AND hcasa.attribute_category = ‘ARCUS’
   AND hcasa.attribute4 IS NOT NULL
   AND hcasa.cust_account_id = c_cust_acct_id_i     — Parameter 1
   AND hcasa.attribute4 = c_custpo_i                — Parameter 2
   AND hcasa.status = ‘A’
   AND hcsua.status = ‘A’
   AND hca.status = ‘A’;
— This API is used to process the customer
po with validations
oe_msg_pub.initialize;
oe_blanket_pub.process_blanket
(
   
p_org_id                 
=> n_organization_id,
   
p_operating_unit         
=> NULL,
   
p_api_version_number     
=> 1.0,
   
x_return_status          
=> x_return_status,
   
x_msg_count              
=> x_msg_count,
   
x_msg_data               
=> x_msg_data,
   
p_header_rec             
=> l_hdr_rec,
 — sold_to_org_id,
order_type_id, context, start_date_active, cust_po_number
   
p_header_val_rec         
=> l_hdr_val_rec,
 
oe_blanket_pub.g_miss_header_val_rec
   
p_line_tbl               
=> l_line_tbl,
 
oe_blanket_pub.g_miss_blanket_line_rec
   
p_line_val_tbl           
=> l_line_val_tbl,
 
oe_blanket_pub.g_miss_blanket_line_val_rec
   
p_control_rec            
=> l_control_rec,
   
x_header_rec             
=> x_header_rec,
   
x_line_tbl               
=> x_line_tbl

);

— By
— Eswaramoorthi M
  • September 27, 2016 | 20 views