Tuesday 27 September 2016

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

No comments:

Post a Comment