API for creating, updating and deleting Pricelist?

qp_price_list_pub.process_price_list

AR Receipt Payment Data Query

SELECT DISTINCT hou.name "Operating Unit Name",
--hou.ORGANIZATION_ID,
  TRUNC (acra.receipt_date) "Receipt Date",
  acra.receipt_number "Receipt Number",
 rcta.trx_number "Invoice Number",
 -- rcta.org_id,
  decode (ara.status,
  'ACC', 'On-Account',
  'APP', 'Applied',
  'UNID', 'Unidentified',
  'UNAPP', 'Unapplied',
  'ACTIVITY', 'ACTIVITY', 'None' ) "Receipt Status",
  hcas.account_number "Customer Number",
  hp.party_name "Customer Name",
  hl1.state "Customer Ship-to State" ,
  hl1.country "Country",
  --apsa.amount_due_remaining
 -- apsa.amount_due_original "Receipt Amount",
  acra.amount "Receipt Amount",
  ara.AMOUNT_APPLIED,
  --apsa.Amount_line_items_original,
  --apsa.tax_original,
  ara.line_applied,
  ara.tax_applied
FROM ar_cash_receipts_all acra,
  ar_receivable_applications_all ara,
  ra_customer_trx_all rcta,
  ar_payment_schedules_all apsa,
  hz_parties hp ,
  hz_cust_accounts hcas ,
  hz_cust_acct_sites_all hcasa ,
  hz_party_sites hps ,
  hz_contact_points hcp ,
  hz_locations hl,
  hr_organization_units hou,
  apps.hz_cust_site_uses_all hcsua1   ,
  apps.hz_cust_acct_sites_all hcasa1   ,
  apps.hz_party_sites hps1             ,
  apps.hz_locations hl1               
WHERE acra.pay_from_customer = hcas.cust_account_id(+)
AND hcas.party_id            = hp.party_id(+)
AND hp.party_id              = hps.party_id
AND hcasa.party_site_id      = hps.party_site_id
AND hcasa.status             = 'A'
AND hps.status               = 'A'
AND hps.party_site_id        = hcp.owner_table_id(+)
AND hcp.contact_point_type   = 'PHONE'
AND hcp.owner_table_name     = 'HZ_PARTY_SITES'
AND hcp.phone_line_type      = 'GEN'
AND hcp.status               = 'A'
AND hps.location_id          = hl.location_id
AND hcasa.org_id             = hou.organization_id
AND hcasa.org_id             = acra.org_id
AND hcasa.org_id             = ara.org_id
AND hcasa.org_id             = rcta.org_id
AND hcasa.org_id             = apsa.org_id
  --AND hcasa.org_id IN (103,235,237,221)
--AND hcasa.org_id               =103
AND ara.cash_receipt_id        =acra.cash_receipt_id
AND ara.applied_customer_trx_id=rcta.customer_trx_id
AND rcta.customer_trx_id       = apsa.customer_trx_id
--AND acra.receipt_number        ='13076687'
AND rcta.ship_to_site_use_id = hcsua1.site_use_id
AND hcsua1.cust_acct_site_id  = hcasa1.cust_acct_site_id
AND hcasa1.party_site_id      = hps1.party_site_id
AND hps1.location_id          = hl1.location_id
AND rcta.org_id = hcsua1.org_id
AND rcta.org_id = hcasa1.org_id
AND to_date((to_char(trunc(acra.receipt_date),'dd-mon-rrrr') ),'dd-mon-rrrr')
BETWEEN to_date('01-Oct-2014','dd-mon-rrrr') and to_date('31-Dec-2014','dd-mon-rrrr')
--AND hl.country ='US'
ORDER BY 2,5,3; 

Query to find account details for item (link between MTL_SYSTEM_ITEMS_FVL and gl_code_combinations)

/* Formatted on 30-01-2015 20:40:51 (QP5 v5.114.809.3010) */
  SELECT   ood.OPERATING_UNIT,
           hou.name OU_NAME,
           ood.ORGANIZATION_CODE,
           ood.ORGANIZATION_NAME,
           msi.segment1 ITEM_NUMBER,
           msi.ITEM_TYPE,
           msi.INVENTORY_ITEM_STATUS_CODE,
              --COST_OF_SALES_ACCOUNT,
              gcc.segment1
           || '.'
           || gcc.segment2
           || '.'
           || gcc.segment3
           || '.'
           || gcc.segment4
           || '.'
           || gcc.segment5
           || '.'
           || gcc.segment6
           || '.'
           || gcc.segment7
           || '.'
           || gcc.segment8
              "COST_ACCOUNT",
              gcc_exp.segment1
           || '.'
           || gcc_exp.segment2
           || '.'
           || gcc_exp.segment3
           || '.'
           || gcc_exp.segment4
           || '.'
           || gcc_exp.segment5
           || '.'
           || gcc_exp.segment6
           || '.'
           || gcc_exp.segment7
           || '.'
           || gcc_exp.segment8
              "EXPENSE_ACCOUNT",
              gcc_s.segment1
           || '.'
           || gcc_s.segment2
           || '.'
           || gcc_s.segment3
           || '.'
           || gcc_s.segment4
           || '.'
           || gcc_s.segment5
           || '.'
           || gcc_s.segment6
           || '.'
           || gcc_s.segment7
           || '.'
           || gcc_s.segment8
              "SALES_ACCOUNT"
    FROM   MTL_SYSTEM_ITEMS_FVL msi,
           gl_code_combinations gcc,
           gl_code_combinations gcc_exp,
           gl_code_combinations gcc_s,
           org_organization_definitions ood,
           hr_operating_units hou
   WHERE       msi.ORGANIZATION_ID = ood.ORGANIZATION_ID
           AND ood.OPERATING_UNIT = hou.ORGANIZATION_ID
           AND msi.COST_OF_SALES_ACCOUNT = gcc.CODE_COMBINATION_ID(+)
           AND msi.EXPENSE_ACCOUNT = gcc_exp.CODE_COMBINATION_ID(+)
           AND msi.SALES_ACCOUNT = gcc_s.CODE_COMBINATION_ID(+)
           AND msi.INVENTORY_ITEM_STATUS_CODE = 'Active'
-- and msi.segment1 ='08661137'--INVENTORY_ITEM_ID ='22143'
ORDER BY   ood.OPERATING_UNIT, ood.ORGANIZATION_CODE, msi.segment1

Query to find items base on OE categories

SELECT   distinct                                                -- mc.category_id,
           mc .description,
           mc.attribute_category,
           ood.OPERATING_UNIT,
           hou.name OU_NAME,
           ood.ORGANIZATION_CODE,
           ood.ORGANIZATION_NAME,
           msi.segment1,
           msi.DESCRIPTION "ITEM_DESCRIPTION"
    FROM   MTL_CATEGORIES mc,
           MTL_ITEM_CATEGORIES mic,
           mtl_system_items_b msi,
           org_organization_definitions ood,
           hr_operating_units hou
   WHERE       mc.category_id = mic.category_id
           AND mic.INVENTORY_ITEM_ID = msi.INVENTORY_ITEM_ID
           AND mic.ORGANIZATION_ID = msi.ORGANIZATION_ID
           AND mic.ORGANIZATION_ID = ood.ORGANIZATION_ID
           AND msi.ORGANIZATION_ID = ood.ORGANIZATION_ID
           and ood.OPERATING_UNIT =  hou.ORGANIZATION_ID
           AND mc.attribute_category = 'ENVS OE Categories'
           AND mc.description IN
                    ('Mem Filt Sep.Membrane Film.Polymer Treated.Super Critical CO2.Oleophobic Grade 8.All',
                     'ESP Electrical.AVC''s.All.All.All.All')

API to delete the Price List (qp_price_list_pub.process_price_list)

CREATE OR REPLACE PROCEDURE APPS.geps_price_list_delete_sql
(
errbuf                      OUT VARCHAR2
,retcode                   OUT NUMBER
)
as
cursor rout_det is
select
*
from
geps_price_list_stg
where process_flag=1;
   gpr_return_status                                 VARCHAR2 (1) := NULL;
   gpr_msg_count                                     NUMBER := 0;
   gpr_msg_data                                      VARCHAR2 (2000);
   gpr_price_list_rec                                qp_price_list_pub.price_list_rec_type;
   gpr_price_list_val_rec                            qp_price_list_pub.price_list_val_rec_type;
   gpr_price_list_line_tbl                           qp_price_list_pub.price_list_line_tbl_type;
   gpr_price_list_line_val_tbl                       qp_price_list_pub.price_list_line_val_tbl_type;
   gpr_qualifiers_tbl                                qp_qualifier_rules_pub.qualifiers_tbl_type;
   gpr_qualifiers_val_tbl                            qp_qualifier_rules_pub.qualifiers_val_tbl_type;
   gpr_pricing_attr_tbl                              qp_price_list_pub.pricing_attr_tbl_type;
   gpr_pricing_attr_val_tbl                          qp_price_list_pub.pricing_attr_val_tbl_type;
   ppr_price_list_rec                                qp_price_list_pub.price_list_rec_type;
   ppr_price_list_val_rec                            qp_price_list_pub.price_list_val_rec_type;
   ppr_price_list_line_tbl                           qp_price_list_pub.price_list_line_tbl_type;
   ppr_price_list_line_val_tbl                       qp_price_list_pub.price_list_line_val_tbl_type;
   ppr_qualifiers_tbl                                qp_qualifier_rules_pub.qualifiers_tbl_type;
   ppr_qualifiers_val_tbl                            qp_qualifier_rules_pub.qualifiers_val_tbl_type;
   ppr_pricing_attr_tbl                              qp_price_list_pub.pricing_attr_tbl_type;
   ppr_pricing_attr_val_tbl                          qp_price_list_pub.pricing_attr_val_tbl_type;
   k                                                 NUMBER := 1;
   j                                                 NUMBER := 1;
   v_list_line_id qp_list_lines_v.LIST_LINE_ID%type;
     l_item_id   varchar2(50);
  l_header_id   Number;
  v_err_msg varchar2(500);
  v_count number;
BEGIN

 FND_FILE.PUT_LINE(FND_FILE.LOG,'Start program');
For c in rout_det
Loop
begin
select inventory_item_id
into
l_item_id
from mtl_system_items_b
where segment1=c.Product_value
and organization_id=104;
select list_header_id into l_header_id from qp_list_headers
where name=c.Price_List_Name  ;
begin
select LIST_LINE_ID into v_list_line_id
from qp_list_lines_v
where LIST_HEADER_ID=l_header_id
and product_attr_value=l_item_id;
exception
when no_data_found then
v_list_line_id := null;
update geps_price_list_stg
set process_flag=4,
error_message='no_data_found in pricelist'
where
Product_value =c.Product_value
and Price_List_Name=c.Price_List_Name
and process_flag=c.process_flag;
commit;
when others then
v_list_line_id := null;
end;

 FND_FILE.PUT_LINE(FND_FILE.LOG,'Start program1');

if v_list_line_id is not null then
 FND_FILE.PUT_LINE(FND_FILE.LOG,'Start program2');
   oe_debug_pub.initialize;
   oe_debug_pub.setdebuglevel (5);
   oe_msg_pub.initialize;
--dbms_output.put_line('after get price list ');
   /* setup the list_header rec for update */
   gpr_price_list_rec.list_header_id                        := l_header_id;
   gpr_price_list_rec.NAME                                  := c.Price_List_Name;
   gpr_price_list_rec.list_type_code                        := 'PRL';
   --gpr_price_list_rec.description                           := '<price_list_description>';
   gpr_price_list_rec.operation                             := qp_globals.g_opr_update;
-- delete the price list line rec
   gpr_price_list_line_tbl (k).list_header_id               := l_header_id;
   gpr_price_list_line_tbl (k).list_line_id                 := v_list_line_id;
   gpr_price_list_line_tbl (k).list_line_type_code          := 'PLL';
   gpr_price_list_line_tbl (k).operation                    := qp_globals.g_opr_delete;
--dbms_output.put_line('before process price list ');
   qp_price_list_pub.process_price_list (p_api_version_number          => 1
                                       , p_init_msg_list               => fnd_api.g_false
                                       , p_return_values               => fnd_api.g_false
                                       , p_commit                      => fnd_api.g_false
                                       , x_return_status               => gpr_return_status
                                       , x_msg_count                   => gpr_msg_count
                                       , x_msg_data                    => gpr_msg_data
                                       , p_price_list_rec              => gpr_price_list_rec
                                       , p_price_list_line_tbl         => gpr_price_list_line_tbl
                                       , p_pricing_attr_tbl            => gpr_pricing_attr_tbl
                                       , x_price_list_rec              => ppr_price_list_rec
                                       , x_price_list_val_rec          => ppr_price_list_val_rec
                                       , x_price_list_line_tbl         => ppr_price_list_line_tbl
                                       , x_price_list_line_val_tbl     => ppr_price_list_line_val_tbl
                                       , x_qualifiers_tbl              => ppr_qualifiers_tbl
                                       , x_qualifiers_val_tbl          => ppr_qualifiers_val_tbl
                                       , x_pricing_attr_tbl            => ppr_pricing_attr_tbl
                                       , x_pricing_attr_val_tbl        => ppr_pricing_attr_val_tbl
                                        );

    IF gpr_return_status = fnd_api.g_ret_sts_success THEN
      COMMIT;
FND_FILE.PUT_LINE(FND_FILE.LOG,'The Item deleted from the price list is Sucessfull');
update geps_price_list_stg
set process_flag=2,
error_message='Success'
where
Product_value =c.Product_value
and Price_List_Name=c.Price_List_Name
and process_flag=c.process_flag;
commit;
    ELSE
FND_FILE.PUT_LINE(FND_FILE.LOG,'The Item deleted from the price list Failed');
      ROLLBACK;
   update geps_price_list_stg
set process_flag=3,
error_message='Error'
where
Product_value =c.Product_value
and Price_List_Name=c.Price_List_Name
and process_flag=c.process_flag;
commit;
END IF;
FND_FILE.PUT_LINE(FND_FILE.LOG,'ss6 ');
end if;
exception when others then
v_err_msg :=SQLERRM;
update geps_price_list_stg
set process_flag=3,
error_message=v_err_msg
where
Product_value =c.Product_value
and Price_List_Name=c.Price_List_Name
and process_flag=c.process_flag;

end;
end loop;
end;
/

Which programs will kick at the time of ship confirmed?

Interface Trip Stop
Commercial Invoice
Packing Slip Report
Bill of Lading

Which programs will kick at the time of Launch Pick Release?

Pick Slip Report

Shipping Exception Report

Auto Pack Report