ORACLE Fusion Cloud, EBS PRO(for Oracle Fusion, EBS Professionals)

Thursday, October 11, 2012

Active Party Relationships SQL query

-------------------------------------------------------------------------------------------
-- SQL statement to derive Active Party relationships related to active account parties
--------------------------------------------------------------------------------------
SELECT hr.relationship_id, hr.subject_id,
       hp.party_number subject_party_number, hr.subject_type,
       subject_table_name, object_id,
       hp.party_name Subject_Party_Name,
       (SELECT party_number
          FROM hz_parties
         WHERE party_id = hr.object_id) object_party_number, object_type,
       object_table_name, hr.party_id rel_party_id,
       (SELECT party_number
          FROM hz_parties
         WHERE party_id = hr.party_id) rel_party_number, relationship_type,
       relationship_code, directional_flag, direction_code, start_date,
       end_date
FROM hz_relationships hr, hz_parties hp, hz_cust_accounts hca
WHERE hp.party_id = hca.party_id
   AND hp.party_id = hr.subject_id
   AND hr.object_id<>hr.subject_id
   AND hr.status = 'A'
   AND hp.status = 'A'
   AND hr.directional_flag = 'F'
   AND TRUNC (hr.end_date) >= TRUNC (SYSDATE)
   AND hca.status = 'A';

Monday, October 1, 2012

How to derive/create code_combination_id(CCID) programatically using fnd_flex_ext.get_ccid

/*----------------------------------------------------------------*/
/* Function: xx_get_ccid_fnc                                 */
/* Program desc: Program creates/derives ccid           */
/* Finds combination_id for given concatenated segment values.   */
/* Combination is automatically created if it does not already   */ /* exist.                                                        */
/* Commit the transaction soon after calling this function since */
/*if a combination is created it will prevent other users creating*/
/* similar combinations on any flexfield until a commit is issued.*/
/*Returns positive combination_id or 0 and sets error if invalid. */
/*----------------------------------------------------------------*/

FUNCTION xx_get_ccid_fnc (p_segment1            IN OUT VARCHAR2,
                          p_segment2            IN OUT VARCHAR2,
                          p_segment3            IN OUT VARCHAR2,
                          p_segment4            IN OUT VARCHAR2,
                          p_segment5            IN OUT VARCHAR2,
                          p_segment6            IN OUT VARCHAR2,
                          p_start_date_active   IN OUT DATE)
   RETURN NUMBER
IS
   l_nsegs               NUMBER := 6;
   l_message             VARCHAR2 (1000) := NULL;
   l_coa_id              NUMBER;
   l_ledger_id           NUMBER := fnd_profile.VALUE ('GL_SET_OF_BKS_ID');
   acctarray             apps.fnd_flex_ext.segmentarray;
   ccid                  NUMBER := 0;
   l_segment1            VARCHAR2 (30) := p_segment1;
   l_segment2            VARCHAR2 (30) := p_segment2;
   l_segment3            VARCHAR2 (30) := p_segment3;
   l_segment4            VARCHAR2 (30) := p_segment4;
   l_segment5            VARCHAR2 (30) := p_segment5;
   l_segment6            VARCHAR2 (30) := p_segment6;
   l_conc_segs           VARCHAR2 (300);
   l_start_date_active   DATE := p_start_date_active;
   l_account             NUMBER := 0;
BEGIN
   -- Concatenate GL Accounting Segment values

   l_conc_segs :=
         l_segment1
      || '.'
      || l_segment2
      || '.'
      || l_segment3
      || '.'
      || l_segment4
      || '.'
      || l_segment5
      || '.'
      || l_segment6;

   --Creates New Accounting Combination if not exist
   SELECT chart_of_accounts_id
     INTO l_coa_id
     FROM gl_ledgers
    WHERE ledger_id = l_ledger_id;

   --Print Concatenated segments
   DBMS_OUTPUT.put_line ('Accounting-Segments Combination=>' || l_conc_segs);

   -- Create/Derive code_combination_id using API
   l_account :=
      fnd_flex_ext.get_ccid (
         application_short_name   => 'SQLGL',
         key_flex_code            => 'GL#',
         structure_number         => l_coa_id,
         validation_date          => TRUNC (NVL (SYSDATE)),
         concatenated_segments    => l_conc_segs);
   -- Get message
   l_message := apps.fnd_message.get;

   -- Print ccid/error details
   IF l_account = 0
   THEN
      DBMS_OUTPUT.put_line (
            'Cannot create CCID for Accounting-Segments=>'
         || l_conc_segs
         || ' , Error details=>'
         || l_message);
   ELSE
      DBMS_OUTPUT.put_line ('Following CCID is created=>' || l_account);
   END IF;
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line ('Error in xx_get_ccid_fnc=>' || SQLERRM);
END xx_get_ccid_fnc;

Thursday, September 13, 2012

iProcurement Bulk Inactivation/Deletion of Catalog Items

-----------------------------------------------------------------------------------------------------------------------------------------------------

To delete items from iProcurement Catalog/Category tables we can use the following APIs

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

--API to delete items from Catalog

ICX_POR_DELETE_CATALOG.delete_items_in_catalog (p_catalog_name IN VARCHAR2);

--API to delete items from Category

ICX_POR_DELETE_CATALOG.delete_items_in_category (

errbuf OUT NOCOPY VARCHAR2,

retcode OUT NOCOPY VARCHAR2,

p_rt_category_id IN NUMBER,

p_category_key IN VARCHAR2);

To delete Item-Category assignment use - INV_ITEM_CATEGORY_PUB.Delete_Category_Assignment standard API. Following is the sample code:-

DECLARE

l_return_status VARCHAR2 (80);

l_error_code NUMBER;

l_msg_count NUMBER;

l_msg_data VARCHAR2 (80);

l_category_id NUMBER;

l_category_set_id NUMBER;

l_inventory_item_id NUMBER;

l_organization_id NUMBER;

BEGIN

SELECT mcs_tl.category_set_id

INTO l_category_set_id

FROM mtl_category_sets_tl mcs_tl

WHERE mcs_tl.category_set_name = 'INV_COLORS_SET';

SELECT mcb.category_id

INTO l_category_id

FROM mtl_categories_b mcb

WHERE mcb.segment1 = 'RED'

AND mcb.structure_id =

(SELECT mcs_b.structure_id

FROM mtl_category_sets_b mcs_b

WHERE mcs_b.category_set_id =

(SELECT mcs_tl.category_set_id

FROM mtl_category_sets_tl mcs_tl

WHERE category_set_name = 'INV_COLORS_SET'));

SELECT DISTINCT (inventory_item_id)

INTO l_inventory_item_id

FROM mtl_system_items_kfv msik

WHERE msik.concatenated_segments = 'Category_Item';

SELECT organization_id

INTO l_organization_id

FROM mtl_parameters

WHERE organization_code = &P_ORG_CODE;

inv_item_category_pub.delete_category_assignment

(p_api_version => 1.0,

p_init_msg_list => fnd_api.g_false,

p_commit => fnd_api.g_true,

x_return_status => l_return_status,

x_errorcode => l_error_code,

x_msg_count => l_msg_count,

x_msg_data => l_msg_data,

p_category_id => l_category_id,

p_category_set_id => l_category_set_id,

p_inventory_item_id => l_inventory_item_id,

p_organization_id => l_organization_id

);

END;

How to create a Quote using ASO_QUOTE_PUB

DECLARE
   -- pragma autonomous_transaction;
   p_api_version_number        NUMBER;
   p_init_msg_list            VARCHAR2 (32767);
   p_commit                             VARCHAR2 (32767);
   p_control_rec                       aso_quote_pub.control_rec_type;
   p_qte_header_rec                 aso_quote_pub.qte_header_rec_type;
   p_hd_price_attributes_tbl    aso_quote_pub.price_attributes_tbl_type;
   p_hd_payment_tbl               aso_quote_pub.payment_tbl_type;
   p_hd_shipment_rec             aso_quote_pub.shipment_rec_type;
   p_hd_freight_charge_tbl     aso_quote_pub.freight_charge_tbl_type;
   p_hd_tax_detail_tbl            aso_quote_pub.tax_detail_tbl_type;
   p_qte_line_tbl                     aso_quote_pub.qte_line_tbl_type;
   p_qte_line_dtl_tbl               aso_quote_pub.qte_line_dtl_tbl_type;
   p_line_attr_ext_tbl              aso_quote_pub.line_attribs_ext_tbl_type;
   p_line_rltship_tbl                aso_quote_pub.line_rltship_tbl_type;
   p_price_adjustment_tbl   aso_quote_pub.price_adj_tbl_type;
   p_price_adj_attr_tbl            aso_quote_pub.price_adj_attr_tbl_type;
   p_price_adj_rltship_tbl       aso_quote_pub.price_adj_rltship_tbl_type;
   p_ln_price_attributes_tbl    aso_quote_pub.price_attributes_tbl_type;
   p_ln_payment_tbl               aso_quote_pub.payment_tbl_type;
   p_ln_shipment_tbl              aso_quote_pub.shipment_tbl_type;
   p_ln_freight_charge_tbl     aso_quote_pub.freight_charge_tbl_type;
   p_ln_tax_detail_tbl            aso_quote_pub.tax_detail_tbl_type;
   x_qte_header_rec              aso_quote_pub.qte_header_rec_type;
   x_qte_line_tbl                   aso_quote_pub.qte_line_tbl_type;
   x_qte_line_dtl_tbl             aso_quote_pub.qte_line_dtl_tbl_type;
   x_hd_price_attributes_tbl aso_quote_pub.price_attributes_tbl_type;
   x_hd_payment_tbl            aso_quote_pub.payment_tbl_type;
   x_hd_shipment_rec           aso_quote_pub.shipment_rec_type;
   x_hd_freight_charge_tbl   aso_quote_pub.freight_charge_tbl_type;
   x_hd_tax_detail_tbl          aso_quote_pub.tax_detail_tbl_type;
   x_line_attr_ext_tbl           aso_quote_pub.line_attribs_ext_tbl_type;
   x_line_rltship_tbl             aso_quote_pub.line_rltship_tbl_type;
   x_price_adjustment_tbl    aso_quote_pub.price_adj_tbl_type;
   x_price_adj_attr_tbl         aso_quote_pub.price_adj_attr_tbl_type;
   x_price_adj_rltship_tbl    aso_quote_pub.price_adj_rltship_tbl_type;
   x_ln_price_attributes_tbl aso_quote_pub.price_attributes_tbl_type;
   x_ln_payment_tbl            aso_quote_pub.payment_tbl_type;
   x_ln_shipment_tbl           aso_quote_pub.shipment_tbl_type;
   x_ln_freight_charge_tbl aso_quote_pub.freight_charge_tbl_type;
   x_ln_tax_detail_tbl         aso_quote_pub.tax_detail_tbl_type;
   v_party_id                      NUMBER;
   v_account_id                  NUMBER;
   v_user_id                        NUMBER;
   v_org_id                         NUMBER;
   v_party_site_id              NUMBER;
   v_order_type_id            NUMBER;
   v_quote_name               VARCHAR2 (200);
   v_currency                    VARCHAR2 (10);
   x_return_status             VARCHAR2 (1);
   x_msg_data                  VARCHAR2 (4000);
   x_msg_count                NUMBER;
   x_cost_quote_header_id      VARCHAR2 (1000);
   p_quote_header_id             NUMBER := &p_quote_hdr_id;
   v_pricelist_id                        NUMBER;
   v_item_num                          VARCHAR2 (30) := &p_item_num;
   v_inv_org_code                    VARCHAR2 (50) := &p_invorg_code;
   v_item_id                             NUMBER;
   v_inv_org_id                      NUMBER;
   v_resp_id                              NUMBER := fnd_profile.value('RESP_ID');
   v_appl_id                              NUMBER := fnd_profile.value('APPL_ID');
BEGIN
   fnd_global.APPS_INITIALIZE (v_user_id, v_resp_id, v_appl_id);
   p_api_version_number := 1.0;
   p_init_msg_list := fnd_api.g_true;
   p_commit := fnd_api.g_false;

   -- P_CONTROL_REC := NULL; Modify the code to initialize this parameter

   SELECT cust_party_id,
          order_type_id,
          SUBSTR (quote_name, 1, 200),
          org_id,
          created_by,
          currency_code,
          quote_source_code,
          resource_id,
          resource_grp_id
     INTO v_party_id,
          v_order_type_id,
          v_quote_name,
          v_org_id,
          v_user_id,
          v_currency,
          p_qte_header_rec.quote_source_code,
          p_qte_header_rec.resource_id,
          p_qte_header_rec.resource_grp_id
     FROM aso_quote_headers_all
    WHERE quote_header_id = p_quote_header_id;

   DBMS_OUTPUT.put_line ('after party id' || v_party_id);

   -- Find the cust account primary bill site's party site id
   BEGIN
      SELECT hca.cust_account_id, cas.party_site_id
        INTO v_account_id, v_party_site_id
        FROM hz_parties party,
             hz_cust_accounts hca,
             hz_cust_acct_sites_all cas,
             hz_cust_site_uses_all csu
       WHERE     party.party_id = v_party_id
             AND hca.party_id = party.party_id
             AND hca.cust_account_id = v_account_id
             AND cas.cust_account_id = hca.cust_account_id
             AND cas.status = 'A'
             AND cas.cust_acct_site_id = csu.cust_acct_site_id
             AND csu.site_use_code = 'BILL_TO'
             AND csu.primary_flag = 'Y'
             AND csu.status = 'A'
             AND csu.org_id = v_org_id;
   EXCEPTION
      WHEN NO_DATA_FOUND
      THEN
         BEGIN
            SELECT ps.party_site_id
              INTO v_party_site_id
              FROM hz_parties party,
                   hz_party_sites ps,
                   hz_party_site_uses psu
             WHERE     party.party_id = v_party_id
                   AND party.party_id = ps.party_id
                   AND ps.party_site_id = psu.party_site_id
                   AND ps.status = 'A'
                   AND psu.site_use_type = 'BILL_TO'
                   AND psu.primary_per_type = 'Y'
                   AND psu.status = 'A';
         EXCEPTION
            WHEN NO_DATA_FOUND
            THEN
               SELECT ps.party_site_id
                 INTO v_party_site_id
                 FROM hz_parties party, hz_party_sites ps
                WHERE     party.party_id = v_party_id
                      AND party.party_id = ps.party_id
                      AND ps.status = 'A'
                      AND ROWNUM < 2;
         END;
   END;

   -- p_qte_header_rec := NULL; Modify the code to initialize this parameter
   -----------------------------------------------------
   -- build the header and shipment rec to pass to Oracle's API
   -----------------------------------------------------
   v_user_id := fnd_global.user_id;
   v_org_id := fnd_global.org_id;
   p_qte_header_rec.created_by := v_user_id;
   p_qte_header_rec.last_updated_by := v_user_id;
   p_qte_header_rec.org_id := v_org_id;
   p_qte_header_rec.quote_source_code := 'Order Capture Quotes';

   p_qte_header_rec.cust_party_id := v_party_id;
   p_qte_header_rec.cust_account_id := v_account_id;
   p_qte_header_rec.invoice_to_cust_account_id := v_account_id;
   p_qte_header_rec.invoice_to_party_site_id := v_party_site_id;
   p_qte_header_rec.invoice_to_cust_party_id := v_party_id;
   p_qte_header_rec.quote_name := SUBSTR ('Test Quote', 1, 80);
   p_qte_header_rec.currency_code := v_currency;
   p_qte_header_rec.order_type_id := v_order_type_id;

   --p_qte_header_rec.quote_status_id;
   --COST ESTIMATION
   SELECT quote_status_id
     INTO p_qte_header_rec.quote_status_id
     FROM aso_quote_statuses_vl
    WHERE status_code = 'DRAFT';

   --p_qte_header_rec.price_list_id ;
   --cost price list
   SELECT list_header_id
     INTO p_qte_header_rec.price_list_id
     FROM qp_list_headers_vl
    WHERE list_header_id = v_pricelist_id;

   --AND list_type_code = 'PRL';

   p_hd_shipment_rec.operation_code := 'CREATE';
   p_hd_shipment_rec.ship_to_party_site_id := v_party_site_id;

   SELECT inventory_item_id, organization_id
     INTO v_item_id, v_inv_org_id
     FROM mtl_system_items_b msi, mtl_parameters mp
    WHERE     msi.segment1 = v_item_num
          AND msi.organization_id = mp.organization_id
          AND mp.organization_code = v_inv_org_code;

   ----------------- LINES ---------------------------------
   P_Qte_Line_Tbl (1).organization_id := v_inv_org_id;
   P_Qte_Line_Tbl (1).operation_code := 'CREATE';
   P_Qte_Line_Tbl (1).inventory_item_id := v_item_id;
   P_Qte_Line_Tbl (1).quantity := 1;
   P_Qte_Line_Tbl (1).uom_code := 'EA';
   P_Qte_Line_Tbl (1).line_category_code := 'ORDER';

   x_return_status := NULL;
   x_msg_count := NULL;
   x_msg_data := NULL;

   DBMS_OUTPUT.put_line ('before creating quote');

   apps.aso_quote_pub.create_quote (p_api_version_number,
                                    p_init_msg_list,
                                    p_commit,
                                    p_control_rec,
                                    p_qte_header_rec,
                                    p_hd_price_attributes_tbl,
                                    p_hd_payment_tbl,
                                    p_hd_shipment_rec,
                                    p_hd_freight_charge_tbl,
                                    p_hd_tax_detail_tbl,
                                    p_qte_line_tbl,
                                    p_qte_line_dtl_tbl,
                                    p_line_attr_ext_tbl,
                                    p_line_rltship_tbl,
                                    p_price_adjustment_tbl,
                                    p_price_adj_attr_tbl,
                                    p_price_adj_rltship_tbl,
                                    p_ln_price_attributes_tbl,
                                    p_ln_payment_tbl,
                                    p_ln_shipment_tbl,
                                    p_ln_freight_charge_tbl,
                                    p_ln_tax_detail_tbl,
                                    x_qte_header_rec,
                                    x_qte_line_tbl,
                                    x_qte_line_dtl_tbl,
                                    x_hd_price_attributes_tbl,
                                    x_hd_payment_tbl,
                                    x_hd_shipment_rec,
                                    x_hd_freight_charge_tbl,
                                    x_hd_tax_detail_tbl,
                                    x_line_attr_ext_tbl,
                                    x_line_rltship_tbl,
                                    x_price_adjustment_tbl,
                                    x_price_adj_attr_tbl,
                                    x_price_adj_rltship_tbl,
                                    x_ln_price_attributes_tbl,
                                    x_ln_payment_tbl,
                                    x_ln_shipment_tbl,
                                    x_ln_freight_charge_tbl,
                                    x_ln_tax_detail_tbl,
                                    x_return_status,
                                    x_msg_count,
                                    x_msg_data);
   DBMS_OUTPUT.put_line ('x_qte_header_rec.quote_header_id = '
                                                || x_qte_header_rec.quote_header_id);
   DBMS_OUTPUT.put_line ('x_return_status = ' || x_return_status);
   DBMS_OUTPUT.put_line ('x_msg_count = ' || x_msg_count);
   DBMS_OUTPUT.put_line ('x_msg_data = ' || x_msg_data);

   IF x_qte_header_rec.quote_header_id IS NOT NULL
   THEN
      x_cost_quote_header_id := x_qte_header_rec.quote_header_id;
   END IF;

   FOR i IN 1 .. x_msg_count
   LOOP
      DBMS_OUTPUT.put_line ('Error ' || fnd_msg_pub.get (i, 'F'));
   END LOOP;

   DBMS_OUTPUT.put_line ('end cost api');
END;
/