Query to find customer site level contact points

Customer Site level contact points

/* *******************************************************************
Modules Involved : AR
Description      : get the customer site level communication details
****************************************************************** */
select HP.PARTY_NAME               PARTY_NAME,
       hps.party_site_number       Site_Number,
       hcp.contact_point_type      Contact_Type,
       hcp.phone_line_type         Phone_Line_Type,
       hcp.phone_country_code      Country_Code,
       hcp.phone_number            Phone_number,
       hcp1.contact_point_type     Contact_Type,
       hcp1.email_address          Email_Address ,
       HCP1.EMAIL_FORMAT           EMAIL_FORMAT,
       HCP1.CONTACT_POINT_PURPOSE  CONTACT_POINT_PURPOSE,
       HL.ADDRESS1||','||HL.ADDRESS2||' '||HL.ADDRESS3||' '||HL.ADDRESS4||' '||HL.CITY||','||HL.STATE||','
       ||hl.POSTAL_CODE||','||hl.country SITE_ADDRESS
  FROM apps.hz_parties             hp,
       apps.hz_party_sites         hps,
       APPS.HZ_CONTACT_POINTS      HCP,
       APPS.HZ_CONTACT_POINTS      HCP1,
       apps.hz_locations           hl
WHERE 1=1  
  AND hp.party_id              = hps.party_id
  AND hcp.owner_table_name     = 'HZ_PARTY_SITES'
  AND hcp.owner_table_id       = hps.party_site_id
  AND hcp.contact_point_type   = 'PHONE'
  AND hcp1.owner_table_name    = 'HZ_PARTY_SITES'
  AND hcp1.owner_table_id      = hps.party_site_id
  and HCP1.CONTACT_POINT_TYPE  = 'EMAIL'  
  and hps.location_id          = hl.location_id
  and HPS.PARTY_SITE_NUMBER    = '100095'
  ;

   

Query to get the order and invoice details

Joins between OM and AR

/* *******************************************************************
Modules Involved : AR, OM
Description      : get the invoices and order details
****************************************************************** */

SELECT ooha.order_number        Order_Number,
       rct.trx_number           Invoice_number,
       rctl.line_type           Invoice_Line_Type,
       msi.segment1             Item_Number,
       rctl.quantity_ordered    Quantity_ordered,
       rctl.quantity_invoiced   Quantity_invoiced,
       rctl.unit_standard_price Unit_standard_price,
       rctl.unit_selling_price  Unit_selling_price,
       RCTL.EXTENDED_AMOUNT     EXTENDED_AMOUNT
       --,rctl.*
  FROM apps.oe_order_headers_all      ooha,
       apps.ra_customer_trx_all       rct,
       APPS.RA_CUSTOMER_TRX_LINES_ALL RCTL,
       apps.mtl_system_items_b          msi
 WHERE 1=1
   AND to_char(ooha.order_number)      = rct.interface_header_attribute1
   AND rct.customer_trx_id             = rctl.customer_trx_id
   and RCTL.INVENTORY_ITEM_ID          = MSI.INVENTORY_ITEM_ID  
   AND msi.organization_id             = 65 -- Master
   and RCT.TRX_NUMBER                  = '284968'
   --and RCTL.INTERFACE_LINE_ATTRIBUTE2  = 'Test_Order_Type'
   ;

Query to find the scheduled concurrent programs

Scheduled concurrent programs

/* *******************************************************************
Modules Involved : Application Object Library (AOL)
Purpose : Finding out the Scheduled concurrent program details from a particular responsibility
Description      : Needs one parameter (responsibility_name)
****************************************************************** */

SELECT   fcr.REQUEST_ID
        ,NVL(fcr.DESCRIPTION,CPT.USER_CONCURRENT_PROGRAM_NAME) CONCURRENT_PROGRAM_NAME
        ,SUBSTR (fcr.ARGUMENT_TEXT, 1, 30) ARGUMENT_TEXT
        ,USR.USER_NAME REQUESTED_BY
        ,RESPT.RESPONSIBILITY_NAME
        ,fcrc.DATE1 START_DATE
        ,fcrc.DATE2 END_DATE
        ,DECODE(fcrc.class_type,
              'P', 'Periodic',
              'S', 'On Specific Days',
              'X', 'Advanced',
              fcrc.CLASS_TYPE
             ) SCHEDULE_TYPE
        ,CASE
         when fcrc.class_type = 'P' then
            'Repeat every ' ||
             substr(fcrc.class_info, 1, instr(fcrc.class_info, ':') - 1) ||
             DECODE(SUBSTR(fcrc.CLASS_INFO, INSTR(fcrc.CLASS_INFO, ':', 1, 1) + 1, 1),
                   'N', ' minutes',
                   'M', ' months',
                   'H', ' hours',
                   'D', ' days') ||
             decode(substr(fcrc.class_info, instr(fcrc.class_info, ':', 1, 2) + 1, 1),
                  'S', ' from the start of the prior run',
                  'C', ' from the completion of the prior run')
         WHEN fcrc.CLASS_TYPE = 'S' THEN
              DECODE(SUBSTR(fcrc.CLASS_INFO, 32, 1), '1', 'Last day of month ') ||
              decode(sign(to_number(substr(fcrc.class_info, 33))),
                    '1',  'Days of week: ' ||
                    decode(substr(fcrc.class_info, 33, 1), '1', 'Su ') ||
                    decode(substr(fcrc.class_info, 34, 1), '1', 'Mo ') ||
                    decode(substr(fcrc.class_info, 35, 1), '1', 'Tu ') ||
                    decode(substr(fcrc.class_info, 36, 1), '1', 'We ') ||
                    decode(substr(fcrc.class_info, 37, 1), '1', 'Th ') ||
                    decode(substr(fcrc.class_info, 38, 1), '1', 'Fr ') ||
                    DECODE(SUBSTR(fcrc.CLASS_INFO, 39, 1), '1', 'Sa '))
        END SCHEDULE,
        fcrc.CLASS_INFO "Class Info"
    FROM fnd_concurrent_requests fcr,
         fnd_concurrent_programs_tl cpt,
         fnd_responsibility_tl RESPT,
         fnd_conc_release_classes fcrc,
         fnd_user usr
   WHERE fcr.concurrent_program_id = cpt.concurrent_program_id
     AND fcr.program_application_id = cpt.application_id
     AND fcr.responsibility_id = respt.responsibility_id
     AND fcr.requested_by = usr.user_id
     AND fcr.STATUS_CODE IN ('Q','I')-- 'P'
     AND RESPT.RESPONSIBILITY_NAME = :RESPONSIBILITY_NAME
     AND fcr.RELEASE_CLASS_APP_ID = fcrc.APPLICATION_ID
     AND fcr.RELEASE_CLASS_ID = fcrc.RELEASE_CLASS_ID
     AND fcrc.CLASS_TYPE IS NOT NULL
    --AND fcr.concurrent_program_id = 36888
    --AND TRUNC (actual_start_date) >= TRUNC (SYSDATE) - 1
ORDER BY 1 DESC
;

Query to find the scheduled concurrent programs details

/* *******************************************************************
Modules Involved : Application Object Library (AOL)
Purpose : Finding out the Scheduled concurrent program details from a particular responsibility
Description      : Needs one parameter (responsibility_name)
****************************************************************** */

SELECT   fcr.REQUEST_ID
        ,NVL(fcr.DESCRIPTION,CPT.USER_CONCURRENT_PROGRAM_NAME) CONCURRENT_PROGRAM_NAME
        ,SUBSTR (fcr.ARGUMENT_TEXT, 1, 30) ARGUMENT_TEXT
        ,USR.USER_NAME REQUESTED_BY
        ,RESPT.RESPONSIBILITY_NAME
        ,fcrc.DATE1 START_DATE
        ,fcrc.DATE2 END_DATE
        ,DECODE(fcrc.class_type,
              'P', 'Periodic',
              'S', 'On Specific Days',
              'X', 'Advanced',
              fcrc.CLASS_TYPE
             ) SCHEDULE_TYPE
        ,CASE
         when fcrc.class_type = 'P' then
            'Repeat every ' ||
             substr(fcrc.class_info, 1, instr(fcrc.class_info, ':') - 1) ||
             DECODE(SUBSTR(fcrc.CLASS_INFO, INSTR(fcrc.CLASS_INFO, ':', 1, 1) + 1, 1),
                   'N', ' minutes',
                   'M', ' months',
                   'H', ' hours',
                   'D', ' days') ||
             decode(substr(fcrc.class_info, instr(fcrc.class_info, ':', 1, 2) + 1, 1),
                  'S', ' from the start of the prior run',
                  'C', ' from the completion of the prior run')
         WHEN fcrc.CLASS_TYPE = 'S' THEN
              DECODE(SUBSTR(fcrc.CLASS_INFO, 32, 1), '1', 'Last day of month ') ||
              decode(sign(to_number(substr(fcrc.class_info, 33))),
                    '1',  'Days of week: ' ||
                    decode(substr(fcrc.class_info, 33, 1), '1', 'Su ') ||
                    decode(substr(fcrc.class_info, 34, 1), '1', 'Mo ') ||
                    decode(substr(fcrc.class_info, 35, 1), '1', 'Tu ') ||
                    decode(substr(fcrc.class_info, 36, 1), '1', 'We ') ||
                    decode(substr(fcrc.class_info, 37, 1), '1', 'Th ') ||
                    decode(substr(fcrc.class_info, 38, 1), '1', 'Fr ') ||
                    DECODE(SUBSTR(fcrc.CLASS_INFO, 39, 1), '1', 'Sa '))
        END SCHEDULE,
        fcrc.CLASS_INFO "Class Info"
    FROM fnd_concurrent_requests fcr,
         fnd_concurrent_programs_tl cpt,
         fnd_responsibility_tl RESPT,
         fnd_conc_release_classes fcrc,
         fnd_user usr
   WHERE fcr.concurrent_program_id = cpt.concurrent_program_id
     AND fcr.program_application_id = cpt.application_id
     AND fcr.responsibility_id = respt.responsibility_id
     AND fcr.requested_by = usr.user_id
     AND fcr.STATUS_CODE IN ('Q','I')-- 'P'
     AND RESPT.RESPONSIBILITY_NAME = :RESPONSIBILITY_NAME
     AND fcr.RELEASE_CLASS_APP_ID = fcrc.APPLICATION_ID
     AND fcr.RELEASE_CLASS_ID = fcrc.RELEASE_CLASS_ID
     AND fcrc.CLASS_TYPE IS NOT NULL
    --AND fcr.concurrent_program_id = 36888
    --AND TRUNC (actual_start_date) >= TRUNC (SYSDATE) - 1
ORDER BY 1 DESC
;

Query to find the profile option values at all levels

The below query is used to get the profile option values at all levels (Site,Application,Responsibility, User).

/* *******************************************************************
Modules Involved: Application object library (AOL)
Purpose         : To get the Profile option information at all levels(Site,Application,Responsibility, User).                
Description     : It also shows the created by and Last updated by infn.Need to pass the profile option name either system name (Ex:'ORG_ID') or
                  user defined name (Ex:'MO: Operating Unit')
 It provides
 1)The value assigned at site level
 2)The value assigned at Application level and Application Name If it is assigned to it.
 3)The value assigned at Responsibility level and Responsibility Name If it is assigned to any resp.
 4)The value assigned at User level and User Name If it is assigned to any user.
****************************************************************** */

select FPO.PROFILE_OPTION_ID, FPOT.PROFILE_OPTION_NAME PROFILE_SHORT_NAME
, fpot.user_profile_option_name profile_name
, DECODE(fpov.level_id,10001,'site',10002,'Appl',10003,'Resp',10004,'User') profile_level
, DECODE(fpov.level_id,10001,null, 10002,fa.application_short_name,10003,fr.responsibility_name,10004,fu.user_name) level_value
, FPOV.PROFILE_OPTION_VALUE PROFILE_VALUE
, FPOV.CREATION_DATE
,(select USER_NAME from FND_USER
   where USER_ID = FPOV.CREATED_BY) "Created By"
, FPOV.LAST_UPDATE_DATE
,(select USER_NAME from FND_USER
   WHERE user_id = fpov.last_updated_by) "Last Update By"
, fpov.*
FROM fnd_profile_option_values fpov
, fnd_profile_options fpo
, fnd_profile_options_tl fpot
, fnd_application fa
, FND_RESPONSIBILITY_TL FR
, FND_USER FU  
where 1=1 and (FPO.PROFILE_OPTION_NAME like NVL(:PROFILE_OPTION_NAME,FPO.PROFILE_OPTION_NAME)
and fpot.user_profile_option_name like nvl(:User_Profile_Option_Name,fpot.user_profile_option_name))
and fpo.profile_option_name=fpot.profile_option_name
and fpo.profile_option_id = fpov.profile_option_id
and fa.application_id(+)=fpov.level_value
AND FR.RESPONSIBILITY_ID(+)=FPOV.LEVEL_VALUE
and FU.USER_ID(+)=FPOV.LEVEL_VALUE
order by 3
;

Query to find all the responsibility names from which we can run a particular concurrent program

The below query lists all the responsibilities from which we can run a particular concurrent program as well as lists all the concurrent programs which you can run from a particular responsibility.

/* *******************************************************************
Modules Involved: Application object library (AOL)
Purpose         : To extract Concurrent Program, Responsibility and Request Group Info.
Description     : We can find the list of Responsibilities from which we can run the specified concurrent program.

This query need any one of the 2 parameters (Conc_Prog_Name, Responsibility_Name):
1)If you pass Conc_Prog_Name, You will get all the Responsibilities from which you can run that Program.
2)If you pass Responsibility_Name, You will get all the Concurrent Programs which you can run from that responsibility.
****************************************************************** */

SELECT fcpt.user_concurrent_program_name,
  frg.request_group_name,
  fcp.concurrent_program_name,
  FRT.RESPONSIBILITY_NAME,
  FAT.APPLICATION_NAME,
  FE.EXECUTABLE_NAME "EXECUTABLE CODE",
  FL.MEANING "EXECUTION METHOD",
  FE.EXECUTION_FILE_NAME
FROM
  APPS.FND_REQUEST_GROUP_UNITS FRGU,
  APPS.FND_CONCURRENT_PROGRAMS FCP,
  APPS.FND_CONCURRENT_PROGRAMS_TL FCPT,
  APPS.FND_REQUEST_GROUPS FRG,
  APPS.FND_EXECUTABLES FE,
  APPS.FND_RESPONSIBILITY FR,
  APPS.FND_RESPONSIBILITY_TL FRT,
  APPS.FND_APPLICATION_TL FAT,
  APPS.FND_LOOKUPS FL
WHERE 1                       = 1
AND fat.application_id        = frgu.application_id
AND frgu.request_unit_id      = fcp.concurrent_program_id
AND frgu.request_group_id     = frg.request_group_id
AND fe.executable_id          = fcp.executable_id
AND fcp.concurrent_program_id = fcpt.concurrent_program_id
AND frg.request_group_id      = fr.request_group_id
AND FR.RESPONSIBILITY_ID      = FRT.RESPONSIBILITY_ID
and FCPT.USER_CONCURRENT_PROGRAM_NAME = NVL(:CONC_PROG_NAME, FCPT.USER_CONCURRENT_PROGRAM_NAME)
and FRT.RESPONSIBILITY_NAME = NVL(:RESPONSIBILITY_NAME,FRT.RESPONSIBILITY_NAME)
and FL.LOOKUP_TYPE = 'CP_EXECUTION_METHOD_CODE'
and FE.EXECUTION_METHOD_CODE = FL.LOOKUP_CODE
;

Query to find all the responsibilities assigned to a user/ all the users who have access to a responsibility

The below query lists either all the responsibilities assigned to a particular user or all the users who have access to a particular responsibility.

/* *******************************************************************
Modules Involved: Application object library (AOL)
Purpose         : User and Responsibilities mapping
Description     : We can find the list of Responsibilities assigned to a specific user and
 list of users have a specific responsibility access.

It needs any one of two parameters (User_Name, Responsibility_Name):
1)If you pass User_Name, You will get all the Responsibilities assigned to that user.
2)If you pass Responsibility_Name, You will get all the Users Who has access to that responsibility.
****************************************************************** */

SELECT FU.USER_ID,FU.USER_NAME, FU.EMAIL_ADDRESS, FR.RESPONSIBILITY_NAME,FURG.start_date, FURG.END_DATE,fu.employee_id
from APPS.FND_USER_RESP_GROUPS_DIRECT FURG
, FND_USER FU
, FND_RESPONSIBILITY_TL FR
where 1=1
and FU.USER_NAME = nvl(:User_Name,FU.USER_NAME)
and FURG.USER_ID = FU.USER_ID
and FURG.RESPONSIBILITY_ID = FR.RESPONSIBILITY_ID
and FR.RESPONSIBILITY_NAME = nvl(:RESPONSIBILITY_NAME,FR.RESPONSIBILITY_NAME)
AND nvl(FURG.END_DATE, sysdate+1)>sysdate
and FR.LANGUAGE = 'US'
ORDER BY 4
;

Single insert script to create a modifier and qualifier using API in R12

This script creates a modifier header, line and qualifier at header level in R12.

DECLARE
l_control_rec QP_GLOBALS.Control_Rec_Type;
l_return_status VARCHAR2(1);
x_msg_count NUMBER;
x_msg_data VARCHAR2(2000);
x_msg_index NUMBER;
l_user_id               NUMBER:=69575;
    l_responsibility_id     NUMBER;
    l_application_id        NUMBER;
l_responsibility_name VARCHAR2(100) ;
i NUMBER :=1;
j NUMBER :=1;
k NUMBER :=1;
l_list_header_id NUMBER;
l_list_line_id NUMBER;
l_qualifier_id NUMBER;
e_problem               EXCEPTION;

l_MODIFIER_LIST_rec QP_Modifiers_PUB.Modifier_List_Rec_Type;
l_MODIFIER_LIST_val_rec QP_Modifiers_PUB.Modifier_List_Val_Rec_Type;
l_MODIFIERS_tbl QP_Modifiers_PUB.Modifiers_Tbl_Type;
l_MODIFIERS_val_tbl QP_Modifiers_PUB.Modifiers_Val_Tbl_Type;
l_QUALIFIERS_tbl QP_Qualifier_Rules_PUB.Qualifiers_Tbl_Type;
l_QUALIFIERS_val_tbl QP_Qualifier_Rules_PUB.Qualifiers_Val_Tbl_Type;
l_PRICING_ATTR_tbl QP_Modifiers_PUB.Pricing_Attr_Tbl_Type;
l_PRICING_ATTR_val_tbl QP_Modifiers_PUB.Pricing_Attr_Val_Tbl_Type;

l_x_MODIFIER_LIST_rec QP_Modifiers_PUB.Modifier_List_Rec_Type;
l_x_MODIFIER_LIST_val_rec QP_Modifiers_PUB.Modifier_List_Val_Rec_Type;
l_x_MODIFIERS_tbl QP_Modifiers_PUB.Modifiers_Tbl_Type;
l_x_MODIFIERS_val_tbl QP_Modifiers_PUB.Modifiers_Val_Tbl_Type;
l_x_QUALIFIERS_tbl QP_Qualifier_Rules_PUB.Qualifiers_Tbl_Type;
l_x_QUALIFIERS_val_tbl QP_Qualifier_Rules_PUB.Qualifiers_Val_Tbl_Type;
l_x_PRICING_ATTR_tbl QP_Modifiers_PUB.Pricing_Attr_Tbl_Type;
l_x_PRICING_ATTR_val_tbl QP_Modifiers_PUB.Pricing_Attr_Val_Tbl_Type;

BEGIN

l_responsibility_name := 'Order Management Super User';
 holx_cyt_util_pkg.get_responsibility_id (l_responsibility_name,
                                                  l_responsibility_id,
                                                  l_application_id
                                                 );

         fnd_global.apps_initialize (l_user_id,
                                     l_responsibility_id,
                                     l_application_id
                                    );
OE_MSG_PUB.Initialize;

/* Create a Modifier header of type 'Discount List' */
            l_MODIFIER_LIST_rec.list_type_code := 'DLT'; -- lookup_code in fnd_lookup_values where lookup_type = 'LIST_TYPE_CODE'
            l_MODIFIER_LIST_rec.name := 'SingleInsert'; -- Modifier Name
            l_MODIFIER_LIST_rec.comments := 'Description - Single Insert Testing'; -- Modifier Description
            l_MODIFIER_LIST_rec.version_no := '1.0';
            l_MODIFIER_LIST_rec.start_date_active := TRUNC(SYSDATE);
            l_MODIFIER_LIST_rec.end_date_active := TRUNC(SYSDATE)+100;
            l_MODIFIER_LIST_rec.currency_code := 'USD'; -- currency_code in fnd_currencies
            l_MODIFIER_LIST_rec.description := 'Single Insert Testing';
            l_MODIFIER_LIST_rec.active_flag := 'Y';
            l_MODIFIER_LIST_rec.automatic_flag := 'Y';
            l_MODIFIER_LIST_rec.pte_code := 'ORDFUL';
            l_MODIFIER_LIST_rec.operation := 'CREATE';--QP_GLOBALS.G_OPR_CREATE;

/* Create a Modifier Line to define a New Price for the inventory item id 2834301 */
l_MODIFIERS_tbl(i).modifier_level_code := 'LINE'; -- lookup_code in fnd_lookup_values where lookup_type = 'MODIFIER_LEVEL_CODE'
l_MODIFIERS_tbl(i).start_date_active := TRUNC(SYSDATE);
l_MODIFIERS_tbl(i).end_date_active := TRUNC(SYSDATE)+100;
l_MODIFIERS_tbl(i).list_line_type_code := 'DIS'; -- lookup_code in fnd_lookup_values where lookup_type = 'LIST_LINE_TYPE_CODE'
l_MODIFIERS_tbl(i).accrual_flag := 'N';
l_MODIFIERS_tbl(i).arithmetic_operator := 'NEWPRICE'; -- lookup_code in fnd_lookup_values where lookup_type = 'AMS_QP_ARITHMETIC_OPERATOR'
l_MODIFIERS_tbl(i).operand := 100; -- New price amount
l_MODIFIERS_tbl(i).product_precedence := 220;
l_MODIFIERS_tbl(i).price_break_type_code := 'POINT'; -- lookup_code in fnd_lookup_values where lookup_type = 'PRICE_BREAK_TYPE_CODE'
l_MODIFIERS_tbl(i).automatic_flag := 'Y';
l_MODIFIERS_tbl(i).override_flag := 'N';
l_MODIFIERS_tbl(i).pricing_phase_id := 2; -- pricing_phase_id in qp_pricing_phases
l_MODIFIERS_tbl(i).pricing_group_sequence := 1; -- Bucket
l_MODIFIERS_tbl(i).operation := 'CREATE';--QP_GLOBALS.G_OPR_CREATE;

l_PRICING_ATTR_tbl(i).product_attribute_context := 'ITEM'; -- prc_context_code in qp_prc_contexts_b where prc_context_type = 'PRODUCT'
l_PRICING_ATTR_tbl(i).product_attribute := 'PRICING_ATTRIBUTE1'; -- segment_mapping_column in qp_segments_b
l_PRICING_ATTR_tbl(i).product_attr_value := '2834301'; -- inventory_item_id in mtl_system_items_b as product_attribute_context is ITEM
l_PRICING_ATTR_tbl(i).product_uom_code := 'EA'; -- uom_code in mtl_units_of_measure
l_PRICING_ATTR_tbl(i).comparison_operator_code := 'BETWEEN';
l_PRICING_ATTR_tbl(i).pricing_attribute_context := 'VOLUME'; -- prc_context_code in qp_prc_contexts_b
l_PRICING_ATTR_tbl(i).pricing_attribute := 'PRICING_ATTRIBUTE10'; -- segment_mapping_column in qp_segments_b
l_PRICING_ATTR_tbl(i).excluder_flag := 'N';
l_PRICING_ATTR_tbl(i).accumulate_flag := 'N';
l_PRICING_ATTR_tbl(i).MODIFIERS_index := 1;
l_PRICING_ATTR_tbl(i).operation := 'CREATE';--QP_GLOBALS.G_OPR_CREATE;

/* Create a Qualifier Record  */
l_QUALIFIERS_tbl(j).excluder_flag := 'N';
l_QUALIFIERS_tbl(j).comparison_operator_code := '=';
l_QUALIFIERS_tbl(j).qualifier_context := 'CUSTOMER'; -- prc_context_code in qp_prc_contexts_b where prc_context_type = 'QUALIFIER'
l_QUALIFIERS_tbl(j).qualifier_attribute := 'QUALIFIER_ATTRIBUTE2'; -- segment_mapping_column in qp_segments_b
l_QUALIFIERS_tbl(j).qualifier_attr_value := '769150';  -- cust_account_id in hz_cust_accounts_all as qualifier_context is CUSTOMER
l_QUALIFIERS_tbl(j).qualifier_grouping_no := 1;
l_QUALIFIERS_tbl(j).qualifier_precedence := 700;
l_QUALIFIERS_tbl(j).start_date_active := TRUNC(SYSDATE);
l_QUALIFIERS_tbl(j).end_date_active := TRUNC(SYSDATE)+100;
l_QUALIFIERS_tbl(j).operation := 'CREATE';--QP_GLOBALS.G_OPR_CREATE;

/* Call the Modifiers Public API to create the modifier header, lines and Header Level Qualifiers */
QP_Modifiers_PUB.Process_Modifiers(p_api_version_number => 1.0,
p_init_msg_list => FND_API.G_FALSE,
p_return_values => FND_API.G_FALSE,
p_commit => FND_API.G_FALSE,
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_MODIFIER_LIST_rec => l_MODIFIER_LIST_rec,
p_MODIFIERS_tbl => l_MODIFIERS_tbl,
p_QUALIFIERS_tbl => l_QUALIFIERS_tbl,
p_PRICING_ATTR_tbl => l_PRICING_ATTR_tbl,
x_MODIFIER_LIST_rec => l_x_MODIFIER_LIST_rec,
x_MODIFIER_LIST_val_rec => l_MODIFIER_LIST_val_rec,
x_MODIFIERS_tbl => l_x_MODIFIERS_tbl,
x_MODIFIERS_val_tbl => l_MODIFIERS_val_tbl,
x_QUALIFIERS_tbl => l_x_QUALIFIERS_tbl,
x_QUALIFIERS_val_tbl => l_QUALIFIERS_val_tbl,
x_PRICING_ATTR_tbl => l_x_PRICING_ATTR_tbl,
x_PRICING_ATTR_val_tbl => l_PRICING_ATTR_val_tbl);

 IF l_return_status != 'S'
                  THEN
                     ROLLBACK;
                     RAISE e_problem;
                  END IF;

               EXCEPTION
                  WHEN e_problem
                  THEN
                     IF (x_msg_count > 0)
                     THEN
                        FOR l_lcv IN 1 .. x_msg_count
                        LOOP
x_msg_data := oe_msg_pub.get(p_msg_index => k, p_encoded => 'F');
                        END LOOP;
DBMS_OUTPUT.PUT_LINE('Error: '||x_msg_data);
END IF;


END;
/