APPS.JAI_CMN_SETUP_PKG SQL Statements

The following lines contain the word 'select', 'insert', 'update' or 'delete':

Line: 40

 select manufacturing , trading
 from   jai_cmn_inventory_orgs
 where  organization_id = cp_organization_id
 and    location_id     = cp_location_id;

Line: 50

select name
from   oe_transaction_types_tl
where  transaction_type_id = p_order_invoice_type_id;

Line: 55

Select name
from   ra_batch_sources_all
where  batch_source_id = p_order_invoice_type_id;

Line: 60

select a.register_code
from   JAI_OM_OE_BOND_REG_HDRS a , JAI_OM_OE_BOND_REG_DTLS b
where  a.organization_id = p_orgn_id
and    a.location_id = p_locn_id
and    a.register_id = b.register_id
and    b.order_flag  = p_order_flag
and    b.order_type_id = p_order_invoice_type_id;

Line: 70

SELECT meaning
FROM   ja_lookups
WHERE  lookup_type = cp_lookup_type --'JAI_REGISTER_TYPE'   /* Modified by Ramananda for removal of SQL LITERALs :bug#4428980*/
AND    lookup_code = v_lookup_code;

Line: 76

Select start_number , prefix , jump_by , end_number
From   JAI_CMN_RG_EXC_INV_NOS
where  organization_id    = p_orgn_id
and    location_id        = p_loc_id
and    fin_year           = p_fin_year
and    order_invoice_type = v_order_type
and    register_code      = v_register_meaning
for update;

Line: 86

SELECT A.Organization_Id, A.Location_Id
FROM   JAI_CMN_INVENTORY_ORGS A
WHERE  A.Tr_Ec_Code IN
(
 SELECT B.Tr_Ec_Code
 FROM   JAI_CMN_INVENTORY_ORGS B
 WHERE  B.Organization_Id = p_organization_id
 AND    B.Location_Id     = p_location_id
);

Line: 100

SELECT A.Organization_Id, A.Location_Id
FROM   JAI_CMN_INVENTORY_ORGS A
WHERE  A.Ec_Code IN
(
 SELECT B.Ec_Code
 FROM   JAI_CMN_INVENTORY_ORGS B
 WHERE  B.Organization_Id = p_organization_id
 AND    B.Location_Id     = p_location_id
)
 AND  nvl(EXCISE_INVNO_AT_EC_CODE, 'N') = 'Y' /*Added by nprashar for bug # 7319628*/;

Line: 113

Select nvl(gp1,0) , nvl(gp2,0)
From   JAI_CMN_RG_EXC_INV_NOS
where  organization_id    = p_orgn_id
and    location_id        = p_loc_id
and    fin_year           = p_fin_year
AND    order_invoice_type IS NULL
AND    register_code      IS NULL;

Line: 123

Select ec_code , master_org_flag , master_organization_id, EXCISE_INVNO_AT_EC_CODE
from   JAI_CMN_INVENTORY_ORGS
where  organization_id = p_organization_id
and    location_id = p_location_id;

Line: 130

select organization_id , EXCISE_INVNO_AT_EC_CODE , location_id
from   JAI_CMN_INVENTORY_ORGS
where  organization_id = p_orgn_id
and    ec_code = p_ec_code
and    master_org_flag = 'Y';

Line: 138

Select tr_ec_code , master_org_flag , master_organization_id
from   JAI_CMN_INVENTORY_ORGS
where  organization_id = p_organization_id
and    location_id = p_location_id;

Line: 145

select organization_id  , location_id
from   JAI_CMN_INVENTORY_ORGS
where  organization_id = p_orgn_id
and    tr_ec_code = p_ec_code
and    master_org_flag = 'Y';

Line: 187

				2) Added if condition to update table JAI_CMN_RG_EXC_INV_NOS for the records where EXCISE_INVNO_AT_EC_CODE = 'N'
					for the manufacturing organizations because such records will not be fetched by cursor c_ec_code_cur so invoice number
					will not be updated to next number for that organization.
				3) FP of bug#7505975
					Issue: Excise Invoice Number for a Trading organization with Master-- Child setup
						 is not using its Master Organization Excise sequence defined.
					Fix: Changes are done to ensure that Trading Org with master- child relation ship will use the
						 master org excise sequence.

Future Dependencies For the release Of this Object:-
(Please add a row in the section below only if your bug introduces a dependency due to spec change/ A new call to a object/
A datamodel change )

--------------------------------------------------------------------------------------------------------------
Version       Bug         Dependencies
---------------------------------------------------------------------------------------------------------------
616.1         3071342     IN60104D1
619.1         3439480     No dependencies introduced - IN60105D2

*************************************************************************************************************/
fnd_file.put_line(fnd_file.log,'Starting Excise Invoice Generation Prg');--bug#7719911

Line: 370

Fnd_File.PUT_LINE(Fnd_File.LOG,'Before update time is ' || to_char(sysdate,'dd-mon-yyyy hh:mi:ss'));

Line: 372

update  JAI_CMN_RG_EXC_INV_NOS
set   last_update_date = last_update_date
where   fin_year           = p_fin_year
AND     order_invoice_type IS NULL
AND     register_code      IS NULL
and     (organization_id, location_id)
      in
    (SELECT A.Organization_Id, A.Location_Id
     FROM   JAI_CMN_INVENTORY_ORGS A
     WHERE  A.Ec_Code IN
       (
         SELECT B.Ec_Code
         FROM   JAI_CMN_INVENTORY_ORGS B
         WHERE  B.Organization_Id = v_act_organization_id
         AND    B.Location_Id     = v_act_location_id
       )
      );

Line: 390

Fnd_File.PUT_LINE(Fnd_File.LOG,'After update time is ' || to_char(sysdate,'dd-mon-yyyy hh:mi:ss'));

Line: 617

      UPDATE JAI_CMN_RG_EXC_INV_NOS
      SET start_number = v_start_number,
      last_update_date = sysdate
      WHERE organization_id = v_act_organization_id
      AND location_id     = v_act_location_id
      AND fin_year        = p_fin_year
      AND order_invoice_type = v_order_type
      AND register_code  =v_register_meaning
      AND transaction_type = v_trans_type_up;

Line: 628

      UPDATE JAI_CMN_RG_EXC_INV_NOS
      SET start_number = v_start_number,
      last_update_date = sysdate
      WHERE organization_id = v_act_organization_id
      AND location_id     = v_act_location_id
      AND fin_year        = p_fin_year
      AND order_invoice_type = v_order_type
      AND register_code  =v_register_meaning;

Line: 649

        UPDATE JAI_CMN_RG_EXC_INV_NOS
        SET start_number = v_start_number
        WHERE organization_id = master_org_rec.organization_id
        AND location_id     = master_org_rec.location_id
        AND fin_year        = p_fin_year
        AND order_invoice_type = v_order_type
        AND register_code    = v_register_meaning;

Line: 661

		UPDATE JAI_CMN_RG_EXC_INV_NOS
        	SET start_number = v_start_number
        	WHERE organization_id = v_act_organization_id
        	AND location_id     = v_act_location_id
        	AND fin_year        = p_fin_year
        	AND order_invoice_type = v_order_type
        	AND register_code    = v_register_meaning;

Line: 670

        	UPDATE JAI_CMN_RG_EXC_INV_NOS
        	SET start_number = v_start_number
        	WHERE organization_id = master_org_rec.organization_id
        	AND location_id     = master_org_rec.location_id
        	AND fin_year        = p_fin_year
        	AND order_invoice_type = v_order_type
        	AND register_code    = v_register_meaning;

Line: 697

        UPDATE JAI_CMN_RG_EXC_INV_NOS
        SET gp1 = v_gp1,
        gp2 = v_gp2
        WHERE organization_id = master_org_rec.organization_id
        AND location_id     = master_org_rec.location_id
        AND fin_year        = p_fin_year
        AND order_invoice_type IS NULL
        AND register_code IS NULL;

Line: 709

		UPDATE JAI_CMN_RG_EXC_INV_NOS
        	SET gp1 = v_gp1,
        	gp2 = v_gp2
        	WHERE organization_id =v_act_organization_id
        	AND location_id     = v_act_location_id
        	AND fin_year        = p_fin_year
        	AND order_invoice_type IS NULL
        	AND register_code IS NULL;

Line: 719

        UPDATE JAI_CMN_RG_EXC_INV_NOS
        SET gp1 = v_gp1,
        gp2 = v_gp2
        WHERE organization_id = master_org_rec.organization_id
        AND location_id     = master_org_rec.location_id
        AND fin_year        = p_fin_year
        AND order_invoice_type IS NULL
        AND register_code IS NULL;

Line: 772

  Select Order_Type_code
    from op_ordr_typ a , op_ordr_hdr b
   Where UPPER(a.order_type) = UPPER(b.order_type)
    and  b.order_id = P_Ordid ;

Line: 779

  SELECT a.register_code
    FROM JAI_OM_OE_BOND_REG_HDRS a, JAI_OM_OE_BOND_REG_DTLS b,sy_orgn_mst c
   WHERE a.organization_id = c.organization_id
     AND UPPER(c.orgn_code)       = UPPER(s_orgn_code)  ------org_changed
     AND a.register_id     = b.register_id
     AND b.order_flag      = 'Y'
     AND b.order_type_id   = (select order_type From Op_Ordr_Hdr where Order_id =
                                 (select order_id From JAI_OPM_SO_PICK_LINES
                                   where bol_id = P_BOL_ID
                                    and bolline_no = P_BOLLINE_NO ));

Line: 793

  SELECT a.register_code
    FROM JAI_OM_OE_BOND_REG_HDRS a, JAI_OM_OE_BOND_REG_DTLS b,org_organization_definitions c
   WHERE a.organization_id = c.organization_id
     AND UPPER(c.organization_code)       = UPPER(s_orgn_code)  ------org_changed
     AND a.register_id     = b.register_id
     AND b.order_flag      = 'Y'
     AND b.order_type_id   = (select order_type From Op_Ordr_Hdr where Order_id =
                                 (select order_id From JAI_OPM_SO_PICK_LINES
                                   where bol_id = P_BOL_ID
                                    and bolline_no = P_BOLLINE_NO ));

Line: 806

  Select meaning
    From ja_lookups
   Where UPPER(lookup_code) LIKE  UPPER(p_register_code)
     And UPPER(lookup_type) = cp_register_type ; --UPPER('JAI_REGISTER_TYPE');    /* Modified by Ramananda for removal of SQL LITERALs :bug#4428980

Line: 813

  SELECT NVL(MAX(DOM_NUMBER),0),NVL(MAX(EXP_NUMBER),0)
    FROM JAI_OPM_EXCISE_INV_NOS
   WHERE UPPER(orgn_code) = UPPER(s_orgn_code) ---org_changed
     AND fin_year  = p_fin_year
     AND order_invoice_type IS Null
     AND register_code IS Null;

Line: 822

  SELECT MAX(a.fin_year)
    FROM JAI_CMN_FIN_YEARS a ,sy_orgn_mst b
   WHERE a.organization_id = b.organization_id
     and UPPER(b.orgn_code) =  UPPER(s_orgn_code) -----org_changed
     and a.fin_active_flag = 'Y';

Line: 831

     SELECT MAX(a.fin_year)
     FROM   JAI_CMN_FIN_YEARS a ,org_organization_definitions b
     WHERE  a.organization_id = b.organization_id
     and upper(b.organization_code) = upper(s_orgn_code)
     and a.fin_active_flag = 'Y';

Line: 841

  Select start_number, end_number, jump_by, prefix
    From JAI_OPM_EXCISE_INV_NOS
   Where UPPER(orgn_code)    = UPPER(s_orgn_code) ---org_changed
     And fin_year                      = p_fin_year
     And UPPER(order_invoice_type) = UPPER(p_batch_name)
     And UPPER(register_code)      = UPPER(p_register_code);

Line: 869

  v_last_update_date      CONSTANT  Date   := SYSDATE; --Added CONSTANT Ramananda for File.Sql.35

Line: 870

  v_last_updated_by       Number := 1774 ;

Line: 871

  v_last_update_login     Number := 233965 ;

Line: 880

  SELECT FROM_WHSE
    FROM OP_ORDR_HDR
   WHERE ORDER_ID = P_Ordid ;

Line: 885

  SELECT LOCATION_ID
    FROM HR_LOCATIONS
   WHERE UPPER(LOCATION_CODE) = UPPER(v_From_Whse) ;

Line: 890

  SELECT ORGANIZATION_ID
    FROM JAI_CMN_INVENTORY_ORGS
   WHERE LOCATION_ID = v_loc_id ;

Line: 896

  SELECT SUBSTR(ORGANIZATION_CODE,1,4)
    FROM ORG_ORGANIZATION_DEFINITIONS
   WHERE ORGANIZATION_ID = v_From_org_id ;

Line: 902

  SELECT B.ORGANIZATION_CODE
  FROM ORG_ORGANIZATION_DEFINITIONS B,IC_WHSE_MST C
  WHERE B.ORGANIZATION_CODE = C.ORGN_CODE
  AND C.WHSE_CODE = v_From_whse_code;

Line: 1114

              UPDATE JAI_OPM_EXCISE_INV_NOS
           SET start_number       = v_start_number,
                     last_update_date   = v_last_update_date,
                     last_updated_by    = v_last_updated_by,
                     last_update_login  = v_last_update_login
               WHERE UPPER(orgn_code)   =  UPPER(V_orgn_code)
                 AND fin_year           = v_fin_year
                 AND UPPER(order_invoice_type) = UPPER(v_order_invoice_type)
                 AND UPPER(register_code)      = UPPER(v_meaning);

Line: 1124

              UPDATE JAI_OPM_EXCISE_INV_NOS
          SET  dom_number = v_gp_1,
                     exp_number = v_gp_2,
                     last_update_date = v_last_update_date,
                     last_updated_by  = v_last_updated_by,
                     last_update_login = v_last_update_login
              WHERE  UPPER(orgn_code) = UPPER(v_orgn_code) ----org_changed
              AND    fin_year        = v_fin_year
              AND    order_invoice_type IS Null
              AND    register_code IS Null;

Line: 1137

SELECT SUBSTR(v_excise_inv_no, instr(v_excise_inv_no, '/', 1, 1) + 1)
into   v_excise_inv_no
from   dual;

Line: 1141

             UPDATE JAI_OPM_SO_PICK_LINES
          SET excise_invoice_no = v_excise_inv_no
        WHERE UPPER(orgn_code) = UPPER(v_orgn_code) ---org_changed
            AND bol_id = p_bol_id
                AND bolline_no = P_BOLLINE_NO ;

Line: 1174

    SELECT Price_List_Id
    FROM JAI_CMN_VENDOR_SITES
    WHERE Vendor_Id = p_vendor_id
    AND Vendor_Site_Id = NVL( p_vendor_site_id, 0 );

Line: 1180

    SELECT Price_List_Id
    FROM JAI_CMN_VENDOR_SITES
    WHERE vendor_Id = p_vendor_id
    AND Vendor_Site_Id = 0;

Line: 1186

    SELECT operand
    FROM qp_List_Lines_v
    WHERE List_header_id = v_price_list_id
    AND product_attribute_context = cp_item --'ITEM'      -- cbabu for Bug# 3083335
    AND product_Id = p_inv_item_id
    AND product_uom_code = p_line_uom
    AND NVL( Start_Date_Active, SYSDATE - 1 ) <= SYSDATE
    AND NVL( End_Date_Active, SYSDATE + 1 ) >= SYSDATE;