Create relation for ATO products

Create relation for ATO products

This will create all entry's in line_detail table and line table and line relation table fro ATO items

      l_model_line_rec.quote_line_id := l_model_quote_line_id;

      l_model_line_rec.quote_header_id := p_active_header_id;

      l_model_line_rec.org_id := l_org_id;

      --l_model_line_rec. line_category_code            := 'ORDER';

      l_model_line_rec.item_type_code := 'MDL';

      l_model_line_rec.organization_id := li_organization_id;

      l_model_line_rec.inventory_item_id := l_cfg_inventory_item_id;

      l_model_line_rec.quantity := 1;

      l_model_line_rec.order_line_type_id := 1028;

      l_model_line_rec.price_list_id := l_cfg_price_list_id;

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

      l_config_rec.quote_line_id := l_model_quote_line_id;

      l_config_rec.qte_line_index := 1;

      l_config_rec.complete_configuration_flag := 'Y';

      l_config_rec.valid_configuration_flag := 'Y';

      l_config_rec.config_header_id := l_new_config_hdr_id;

      l_config_rec.config_revision_num := l_new_config_rev_nbr;

      l_config_rec.quote_line_detail_id := l_aso_quote_line_detail_id;

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

      --control details --

      l_control_rec.pricing_request_type := 'ASO';

      l_control_rec.calculate_freight_charge_flag := 'Y';

      l_control_rec.calculate_tax_flag := 'Y';

      l_control_rec.header_pricing_event := 'BATCH';

      l_control_rec.price_mode := 'ENTIRE_QUOTE';

      l_return_message := NULL;

      l_return_status := NULL;

      l_msg_count := NULL;

      l_msg_data := NULL;

      aso_cfg_pub.get_config_details (

         p_api_version_number   => c_api_version1,

         p_init_msg_list        => l_init_msg_list,

         p_commit               => l_commit,

         p_control_rec          => l_control_rec,

         p_config_rec           => l_config_rec,

         p_model_line_rec       => l_model_line_rec,

         p_config_hdr_id        => l_new_config_hdr_id,

         p_config_rev_nbr       => l_new_config_rev_nbr,

         p_quote_header_id      => p_active_header_id,

         x_return_status        => l_return_status,

         x_msg_count            => l_msg_count,

         x_msg_data             => l_msg_data

      );

Finding ORG_ID and Operating UNIT detail based MO:Operating UNIT profile

=> Query to Find Operating Unit and Org_id details

SELECT * FROM fnd_profile_options_vl fpo,

fnd_profile_option_values fpov,

applsys.fnd_responsibility_tl fr,

hr_operating_units hou

WHERE profile_option_name = 'ORG_ID'

AND fpo.profile_option_id = fpov.profile_option_id

AND fpov.level_value = fr.responsibility_id

AND hou.organization_id = fpov.profile_option_value

AND fr.responsibility_name ='IBE_CUSTOMER'

AND fr.language='US';

=> How to get ORG ID and Organization Details:  

SELECT fr.responsibility_name, fpov.profile_option_value orgid, NAME org_name
FROM fnd_profile_options_vl fpo,
fnd_profile_option_values fpov,
applsys.fnd_responsibility_tl fr,
hr_operating_units hou
WHERE UPPER (fpo.user_profile_option_name) LIKE UPPER ('MO%OPERATIN%')
AND profile_option_name = 'ORG_ID'
AND fpo.profile_option_id = fpov.profile_option_id
AND fpov.level_value = fr.responsibility_id
AND hou.organization_id = fpov.profile_option_value

=>Finding org id based on Organization name:

select * from hr_operating_units where name ='Vision Operation'

=>Finding List of SHIP_TO and BILL_TO address based on customer  

CURSOR get_party_bill_to_cur
      IS
         SELECT   hps.party_site_id                                
   FROM   hz_party_sites hps,
                  hz_cust_accounts hca,
                  hz_parties hp,
                  hz_cust_acct_sites_all hcas,
                  hz_cust_site_uses_all hcsu
          WHERE       hps.party_id = hp.party_id
                  AND hca.cust_account_id = hcas.cust_account_id
                  AND hps.party_site_id = hcas.party_site_id
                  AND hcsu.cust_acct_site_id = hcas.cust_acct_site_id
                  AND hcsu.site_use_code = 'BILL_TO'
                  AND hcsu.primary_flag = 'Y'
                  AND hcsu.status = 'A'
                  AND hca.cust_account_id =P_cust_acct_id
                  AND hcas.org_id = p_org_id;
-------------------------------------------------------------------------------
      CURSOR get_party_ship_to_cur
      IS
         SELECT   hps.party_site_id                               
           FROM   hz_party_sites hps,
                  hz_cust_accounts hca,
                  hz_parties hp,
                  hz_cust_acct_sites_all hcas,
                  hz_cust_site_uses_all hcsu
          WHERE       hps.party_id = hp.party_id
                  AND hca.cust_account_id = hcas.cust_account_id
                  AND hps.party_site_id = hcas.party_site_id
                  AND hcsu.cust_acct_site_id = hcas.cust_acct_site_id
                  AND hcsu.site_use_code = 'SHIP_TO'
                  --AND hcsu.primary_flag = 'Y'
                  AND hcsu.status = 'A'
                  AND hca.cust_account_id =P_cust_acct_id
                  AND hcas.org_id = p_org_id;

Create Quote API in R12

  declare

 

l_org_id varchar2(1254);

li_inventory_item_id NUMBER;

li_currency_code varchar2(1254);

li_uom_code varchar2(1254);

li_quantity NUMBER;

li_QUOTE_CATEGORY_CODE varchar2(1254);

li_line_list_price  varchar2(2000);

li_order_type_id varchar2(2000);

li_price_list_id  NUMBER;

li_quote_source_code  varchar2(2000);

li_party_id  NUMBER;

li_payment_term_id   NUMBER;

li_cust_account_id NUMBER;

li_invoice_to_cust_account_id  NUMBER;

li_invoice_to_party_site_id NUMBER;

li_invoice_to_party_id  NUMBER;

li_invoice_to_cust_party_id NUMBER;

li_total_quote_price  NUMBER;

li_total_list_price NUMBER;

li_total_adjusted_amount  NUMBER;

ln_line_number  NUMBER;

l_quote_line_id  NUMBER;

l_ref_line_id  number;

x_relationship_id number;

x_return_status  VARCHAR2(2000);

x_msg_count  VARCHAR2(2000);

x_msg_data   VARCHAR2(2000);

l_px_QUOTE_LINE_DETAIL_ID  NUMBER;

px_QUOTE_LINE_DETAIL_ID  NUMBER;

---create quote

l_Quote_status_id NUMBER;

   l_control_rec ASO_QUOTE_PUB.Control_Rec_Type;

    l_qte_header_rec ASO_QUOTE_PUB.Qte_Header_Rec_Type;

    l_qte_line_rec ASO_QUOTE_PUB.Qte_Line_Rec_Type;

    l_qte_line_tbl ASO_QUOTE_PUB.Qte_Line_Tbl_Type;

    l_qte_line_dtl_tbl ASO_QUOTE_PUB.Qte_Line_Dtl_Tbl_Type;

    -- l_hd_Price_Attr_Tbl ASO_QUOTE_PUB.Price_Attributes_Tbl_Type;

    l_hd_payment_tbl ASO_QUOTE_PUB.Payment_Tbl_Type;

    l_payment_rec ASO_QUOTE_PUB.Payment_Rec_Type;

    l_hd_shipment_rec ASO_QUOTE_PUB.Shipment_Rec_Type;

    -- l_hd_freight_charge_tbl ASO_QUOTE_PUB.Freight_Charge_Tbl_Type;

    l_hd_tax_detail_tbl ASO_QUOTE_PUB.Tax_Detail_Tbl_Type;

    l_tax_detail_rec ASO_QUOTE_PUB.Tax_Detail_Rec_Type;

    l_tax_control_rec ASO_TAX_INT.Tax_control_rec_type;

    l_Line_Attr_Ext_Tbl ASO_QUOTE_PUB.Line_Attribs_Ext_Tbl_Type;

    l_line_rltship_tbl ASO_QUOTE_PUB.Line_Rltship_Tbl_Type;

    l_Price_Adjustment_Tbl ASO_QUOTE_PUB.Price_Adj_Tbl_Type;

    l_Price_Adj_Attr_Tbl ASO_QUOTE_PUB.Price_Adj_Attr_Tbl_Type;

    l_price_adj_rltship_tbl ASO_QUOTE_PUB.Price_Adj_Rltship_Tbl_Type;

    l_ln_Price_Attr_Tbl ASO_QUOTE_PUB.Price_Attributes_Tbl_Type;

    l_ln_payment_tbl ASO_QUOTE_PUB.Payment_Tbl_Type;

    l_ln_shipment_tbl ASO_QUOTE_PUB.Shipment_Tbl_Type;

    l_ln_freight_charge_tbl ASO_QUOTE_PUB.Freight_Charge_Tbl_Type;

    l_ln_tax_detail_tbl ASO_QUOTE_PUB.Tax_Detail_Tbl_Type;

    lx_qte_header_rec ASO_QUOTE_PUB.Qte_Header_Rec_Type;

    lx_qte_line_tbl ASO_QUOTE_PUB.Qte_Line_Tbl_Type;

    lx_qte_line_dtl_tbl ASO_QUOTE_PUB.Qte_Line_Dtl_Tbl_Type;

    lx_hd_Price_Attr_Tbl ASO_QUOTE_PUB.Price_Attributes_Tbl_Type;

    lx_hd_payment_tbl ASO_QUOTE_PUB.Payment_Tbl_Type;

    lx_hd_shipment_rec ASO_QUOTE_PUB.Shipment_Rec_Type;

    lx_hd_freight_charge_tbl ASO_QUOTE_PUB.Freight_Charge_Tbl_Type;

    lx_hd_tax_detail_tbl ASO_QUOTE_PUB.Tax_Detail_Tbl_Type;

    lx_Line_Attr_Ext_Tbl ASO_QUOTE_PUB.Line_Attribs_Ext_Tbl_Type;

    lx_line_rltship_tbl ASO_QUOTE_PUB.Line_Rltship_Tbl_Type;

    lx_Price_Adjustment_Tbl ASO_QUOTE_PUB.Price_Adj_Tbl_Type;

    lx_Price_Adj_Attr_Tbl ASO_QUOTE_PUB.Price_Adj_Attr_Tbl_Type;

    lx_price_adj_rltship_tbl ASO_QUOTE_PUB.Price_Adj_Rltship_Tbl_Type;

    lx_ln_Price_Attr_Tbl ASO_QUOTE_PUB.Price_Attributes_Tbl_Type;

    lx_ln_payment_tbl ASO_QUOTE_PUB.Payment_Tbl_Type;

    lx_ln_shipment_tbl ASO_QUOTE_PUB.Shipment_Tbl_Type;

    lx_ln_freight_charge_tbl ASO_QUOTE_PUB.Freight_Charge_Tbl_Type;

    lx_ln_tax_detail_tbl ASO_QUOTE_PUB.Tax_Detail_Tbl_Type;

    lx_hd_shipment_tbl ASO_QUOTE_PUB.Shipment_Tbl_Type;

    lX_return_status VARCHAR2(1);

    lx_msg_count NUMBER;

    l_quote_number NUMBER;

    lx_msg_data VARCHAR2(2000);

    my_message VARCHAR2(2000);

    l_file varchar2(2000);

    lnx_quote_line_id NUMBER;

/*

cursor config_cur

is

select distinct config_header_id

from  oe_order_lines_all

where header_id=l_header_id;

*/

---create quote line information start here

--l_ln_payment_tbl ASO_QUOTE_PUB.Payment_Tbl_Type;

--l_ln_shipment_tbl ASO_QUOTE_PUB.Shipment_Tbl_Type;

--l_ln_freight_charge_tbl ASO_QUOTE_PUB.Freight_Charge_Tbl_Type;

--l_ln_tax_detail_tbl ASO_QUOTE_PUB.Tax_Detail_Tbl_Type;

l_hd_Sales_Credit_Tbl ASO_QUOTE_PUB.Sales_Credit_Tbl_Type:= ASO_QUOTE_PUB.G_MISS_Sales_Credit_Tbl;

l_ln_Sales_Credit_Tbl ASO_QUOTE_PUB.Sales_Credit_Tbl_Type:= ASO_QUOTE_PUB.G_MISS_Sales_Credit_Tbl;

lx_hd_Sales_Credit_Tbl ASO_QUOTE_PUB.Sales_Credit_Tbl_Type;

lx_Quote_Party_Tbl ASO_QUOTE_PUB.Quote_Party_Tbl_Type;

lX_Ln_Sales_Credit_Tbl ASO_QUOTE_PUB.Sales_Credit_Tbl_Type;

lX_Ln_Quote_Party_Tbl ASO_QUOTE_PUB.Quote_Party_Tbl_Type;

--l_payment_rec ASO_QUOTE_PUB.Payment_Rec_Type;

l_shipment_rec ASO_QUOTE_PUB.Shipment_Rec_Type;

--l_tax_detail_rec ASO_QUOTE_PUB.Tax_Detail_Rec_Type;

lX_hd_Attr_Ext_Tbl ASO_QUOTE_PUB.Line_Attribs_Ext_Tbl_Type;

l_qte_line_dtl_rec  ASO_QUOTE_PUB.QTE_LINE_DTL_REC_TYPE;

lc_last_update_date date;

---relatioship

l_line_rtlship_rec  ASO_QUOTE_PUB.LINE_RLTSHIP_Rec_Type ;

qte_lin NUMBER;

my_message VARCHAR2(2000);

BEGIN

--l_org_id := fnd_profile.VALUE('ORG_ID');

l_org_id:=204;

dbms_application_info.set_client_info(204);

fnd_global.APPS_INITIALIZE (1111, 50677, 671);

--user_id,responsibility_id,application id

mo_global.init('QOT');

--mo_global.set_policy_context('S',l_org_id); --For singl OU

 

 mo_global.set_policy_context('S',FND_GLOBAL.ORG_ID); --For Multiple OU

  commit;

    select Quote_status_id into l_Quote_status_id

from aso_quote_statuses_b where

status_code='STORE DRAFT';

    l_control_rec.CALCULATE_TAX_FLAG := 'Y';

    l_control_rec.CALCULATE_FREIGHT_CHARGE_FLAG := 'Y';

    l_control_rec.pricing_request_type := 'ASO';

    l_control_rec.header_pricing_event := 'BATCH';

    l_control_rec.last_update_date := SYSDATE;

  

l_qte_header_rec.last_update_date := SYSDATE;

l_qte_header_rec.quote_name := 'LSP Quote test';

select Max(Quote_number) into l_quote_number from aso_quote_headers_all;

l_quote_number:=l_quote_number+1;

--l_qte_header_rec.quote_number :=l_quote_number;

l_qte_header_rec.quote_source_code :='IStore Account';

l_qte_header_rec.currency_code:='USD';

l_qte_header_rec.party_id:=12345;

l_qte_header_rec.cust_account_id:=4;

l_qte_header_rec.invoice_to_cust_account_id:=4;

l_qte_header_rec.invoice_to_party_site_id:=369;

l_qte_header_rec.quote_status_id:=l_Quote_status_id;

l_qte_header_rec.price_list_id:=6008;

l_qte_header_rec.INVOICE_TO_PARTY_ID:=12345;

l_qte_header_rec.ORDER_TYPE_ID:=1003;

l_qte_header_rec.QUOTE_CATEGORY_CODE:='MIXED';

---- SHIPPING details

l_hd_shipment_rec.ship_to_party_id := 12345;

l_hd_shipment_rec.ship_to_party_site_id := 369;

l_hd_shipment_rec.ship_to_cust_account_id := 4;

---- PAYMENT details (for example default a payment term)

l_hd_Payment_Tbl(1).operation_code:= 'CREATE';

l_hd_Payment_Tbl(1).payment_term_id:= 1000;

  

    ----------------- LINES ---------------------------------

    l_Qte_Line_Tbl(1).organization_id := 205;

    l_Qte_Line_Tbl(1).operation_code := 'CREATE';

    l_Qte_Line_Tbl(1).inventory_item_id :=111;

    l_Qte_Line_Tbl(1).quantity :=1;

    l_Qte_Line_Tbl(1).uom_code := 'Ea';

     l_Qte_Line_Tbl(1).org_id := 204;

   

---------create quote    

  --dbms_output.put_line('begin for the create quote header id ');

--commit;

    ASO_QUOTE_PUB.Create_Quote(

        p_api_version_number => 1.0,

        p_init_msg_list => FND_API.G_TRUE,

      

        p_control_rec => l_control_rec,

        p_qte_header_rec => l_qte_header_rec,

        P_Qte_Line_Tbl => l_Qte_Line_Tbl,

        P_hd_Payment_Tbl => l_hd_Payment_Tbl,

        P_hd_Shipment_Rec => l_hd_Shipment_Rec,

        P_hd_Tax_Detail_Tbl => l_hd_Tax_Detail_Tbl,

        x_Qte_Header_Rec => lx_qte_header_rec,

        X_Qte_Line_Tbl => lx_Qte_Line_Tbl,

        X_Qte_Line_Dtl_Tbl => lx_Qte_Line_Dtl_Tbl,

        X_hd_Price_Attributes_Tbl => lx_hd_Price_Attr_Tbl,

        X_hd_Payment_Tbl => lx_hd_Payment_Tbl,

        X_hd_Shipment_Rec => lx_hd_Shipment_Rec,

        X_hd_Freight_Charge_Tbl => lx_hd_Freight_Charge_Tbl,

        X_hd_Tax_Detail_Tbl => lx_hd_Tax_Detail_Tbl,

        x_Line_Attr_Ext_Tbl => lx_Line_Attr_Ext_Tbl,

        X_line_rltship_tbl => lx_line_rltship_tbl,

        X_Price_Adjustment_Tbl => lx_Price_Adjustment_Tbl,

        X_Price_Adj_Attr_Tbl => lx_Price_Adj_Attr_Tbl,

        X_Price_Adj_Rltship_Tbl => lx_Price_Adj_Rltship_Tbl,

        X_ln_Price_Attributes_Tbl => lx_ln_Price_Attr_Tbl,

        X_ln_Payment_Tbl => lx_ln_Payment_Tbl,

        X_ln_Shipment_Tbl => lx_ln_Shipment_Tbl,

        X_ln_Freight_Charge_Tbl => lx_ln_Freight_Charge_Tbl,

        X_ln_Tax_Detail_Tbl => lx_ln_Tax_Detail_Tbl,

        X_Return_Status => lx_Return_Status,

        X_Msg_Count => lx_Msg_Count,

        X_Msg_Data => lx_Msg_Data);

       

--dbms_output.put_line(lx_Qte_Line_Dtl_Tbl(i).quote_line_id);

    fnd_msg_pub.count_and_get(

    p_encoded => 'F',

    p_count => lx_msg_count,

    p_data => lx_msg_data);

commit;

    dbms_output.put_line('no. of FND messages :'||lx_msg_count);

    for k in 1 .. lx_msg_count loop

    lx_msg_data := fnd_msg_pub.get(

    p_msg_index => k,

    p_encoded => 'F');

    dbms_output.put_line('Error msg: '||substr(lx_msg_data,1,240));

   

    end loop;

    dbms_output.put_line(lx_return_status);

    dbms_output.put_line(to_char(lx_msg_count));

    dbms_output.put_line(lx_msg_data);

    dbms_output.put_line('qte_header_id: '||to_char(lx_qte_header_rec.quote_header_id));

    dbms_output.put_line('end');

   

    commit;

    end;