[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT
CONCURRENT_PROGRAM_ID,
NVL(ENABLE_TRACE
,'N')
FROM
FND_CONCURRENT_REQUESTS
WHERE REQUEST_ID = P_REQUEST_ID;
SELECT
A.SID,
A.SERIAL#,
B.SPID
FROM
V$SESSION A,
V$PROCESS B
WHERE AUDSID = USERENV('SESSIONID')
AND A.PADDR = B.ADDR;
SELECT
NAME
FROM
V$DATABASE;
FOR org_rec IN (SELECT
NAME
FROM
HR_ALL_ORGANIZATION_UNITS
WHERE ORGANIZATION_ID = P_ORGANIZATION_ID) LOOP
P_ORGANIZATION_NAME := ORG_REC.NAME;
FOR loc_rec IN (SELECT
DESCRIPTION,
ADDRESS_LINE_1,
ADDRESS_LINE_2,
ADDRESS_LINE_3
FROM
HR_LOCATIONS
WHERE LOCATION_ID = P_LOCATION_ID) LOOP
P_DESCRIPTION := LOC_REC.DESCRIPTION;
FOR ec_rec IN (SELECT
EC_CODE,
EXCISE_DUTY_COMM,
EXCISE_DUTY_RANGE,
EXCISE_DUTY_DIVISION,
EXCISE_DUTY_CIRCLE
FROM
JAI_CMN_INVENTORY_ORGS
WHERE ORGANIZATION_ID = P_ORGANIZATION_ID
AND LOCATION_ID = P_LOCATION_ID) LOOP
P_EC_CODE := EC_REC.EC_CODE;
SELECT
SET_OF_BOOKS_ID
FROM
ORG_ORGANIZATION_DEFINITIONS
WHERE ORGANIZATION_ID = CP_ORGANIZATION_ID
AND ROWNUM = 1;
SELECT
A.QTY_RECEIVED * B.PO_UNIT_PRICE
FROM
JAI_RCV_LINES A,
RCV_TRANSACTIONS B
WHERE A.TRANSACTION_ID = B.TRANSACTION_ID;
FOR c1 IN (SELECT
RTL.TAX_AMOUNT,
RTL.TAX_RATE TR,
RT.SHIPMENT_LINE_ID,
RTL.TAX_TYPE,
RT.ORGANIZATION_ID ORGANIZATION_ID,
RT.CURRENCY_CONVERSION_TYPE,
RT.CURRENCY_CONVERSION_RATE,
RT.CURRENCY_CONVERSION_DATE,
RT.CURRENCY_CODE CCODE,
JTC.ADHOC_FLAG
FROM
JAI_CMN_RG_23AC_II_TRXS RG23,
RCV_TRANSACTIONS RT,
JAI_RCV_LINE_TAXES RTL,
JAI_CMN_TAXES_ALL JTC
WHERE RG23.EXCISE_INVOICE_NO = cf_assessable_valueformula.EXCISE_INVOICE_NO
AND RG23.EXCISE_INVOICE_DATE = cf_assessable_valueformula.EXCISE_INVOICE_DATE
AND RG23.ORGANIZATION_ID = cf_assessable_valueformula.ORGANIZATION_ID
AND RG23.LOCATION_ID = cf_assessable_valueformula.LOCATION_ID
AND RG23.REGISTER_TYPE = P_REGISTER_TYPE
AND TRUNC(RG23.CREATION_DATE) BETWEEN NVL(P_TRN_FROM_DATE
,TRUNC(RG23.CREATION_DATE))
AND NVL(P_TRN_TO_DATE
,TRUNC(SYSDATE))
AND RT.TRANSACTION_ID = TO_NUMBER(RG23.RECEIPT_REF)
AND JTC.TAX_ID = RTL.TAX_ID
AND RT.SHIPMENT_LINE_ID = RTL.SHIPMENT_LINE_ID
AND NVL(RTL.TAX_AMOUNT
,0) <> 0
AND NVL(RTL.MODVAT_FLAG
,'N') = 'Y'
AND RTL.TAX_TYPE IN ( LV_TAX_TYPE_EXCISE , LV_TAX_TYPE_EXC_ADDITIONAL , LV_TAX_TYPE_EXC_OTHER , LV_TAX_TYPE_CVD , LV_TAX_TYPE_ADDITIONAL_CVD )
AND NVL(JTC.MOD_CR_PERCENTAGE
,0) <> 0) LOOP
OPEN CUR_SOB_ID(CP_ORGANIZATION_ID => C1.ORGANIZATION_ID);
SELECT
COUNT(1)
FROM
JAI_RCV_LINE_TAXES JRL,
RCV_TRANSACTIONS RCVT
WHERE RCVT.TRANSACTION_ID = TO_NUMBER(CP_RECEIPT_ID)
AND RCVT.TRANSACTION_TYPE = LV_RCV_TRANSACTION_TYPE
AND RCVT.SHIPMENT_LINE_ID = JRL.SHIPMENT_LINE_ID
AND UPPER(JRL.TAX_TYPE) LIKE UPPER(LV_TAX_TYPE_EXCISE);
SELECT
COUNT(1)
FROM
JAI_RCV_LINE_TAXES JRL,
RCV_TRANSACTIONS RCVT
WHERE RCVT.TRANSACTION_ID = TO_NUMBER(CP_RECEIPT_ID)
AND RCVT.TRANSACTION_TYPE = LV_RCV_TRANSACTION_TYPE
AND RCVT.SHIPMENT_LINE_ID = JRL.SHIPMENT_LINE_ID
AND UPPER(JRL.TAX_TYPE) LIKE LV_TAX_TYPE_CVD;
SELECT
DISTINCT
JU.EC_CODE
FROM
RCV_TRANSACTIONS RCVT,
RCV_SHIPMENT_HEADERS RCVSH,
JAI_CMN_INVENTORY_ORGS JU
WHERE RCVT.TRANSACTION_ID = CP_RECEIPT_ID
AND RCVT.TRANSACTION_TYPE = LV_RCV_TRANSACTION_TYPE
AND RCVT.SHIPMENT_HEADER_ID = RCVSH.SHIPMENT_HEADER_ID
AND RCVSH.RECEIPT_SOURCE_CODE = CP_RECEIPT_SOURCE_CODE
AND RCVSH.ORGANIZATION_ID = JU.ORGANIZATION_ID;
SELECT
JHRU.EC_CODE
FROM
JAI_CMN_INVENTORY_ORGS JHRU
WHERE ORGANIZATION_ID = ABS(VENDOR_ID)
AND LOCATION_ID = ABS(VENDOR_SITE_ID);
SELECT
HRU.NAME
FROM
RCV_TRANSACTIONS RCVT,
RCV_SHIPMENT_HEADERS RCVSH,
HR_ORGANIZATION_UNITS HRU
WHERE RCVT.TRANSACTION_ID = P_RECEIPT_ID
AND RCVT.TRANSACTION_TYPE = LV_RCV_TRANSACTION_TYPE
AND RCVT.SHIPMENT_HEADER_ID = RCVSH.SHIPMENT_HEADER_ID
AND RCVSH.RECEIPT_SOURCE_CODE = CP_RECEIPT_SOURCE_CODE
AND RCVSH.ORGANIZATION_ID = HRU.ORGANIZATION_ID;
SELECT
HRU.NAME
FROM
JAI_CMN_INVENTORY_ORGS JHRU,
HR_ALL_ORGANIZATION_UNITS HRU
WHERE JHRU.ORGANIZATION_ID = ABS(VENDOR_ID)
AND JHRU.LOCATION_ID = ABS(VENDOR_SITE_ID)
AND HRU.ORGANIZATION_ID = JHRU.ORGANIZATION_ID;
SELECT
POV.VENDOR_NAME
FROM
JAI_CMN_RG_23AC_II_TRXS RG23,
PO_VENDORS POV
WHERE RG23.VENDOR_ID = POV.VENDOR_ID
AND RG23.VENDOR_ID = CP_VENDOR_ID;
SELECT
DECODE('Y'
,JHRU.MANUFACTURING
,'Manufacturing'
,JHRU.TRADING
,'Dealer')
FROM
JAI_CMN_INVENTORY_ORGS JHRU,
RCV_TRANSACTIONS RCVT,
RCV_SHIPMENT_HEADERS RCVSH
WHERE RCVT.TRANSACTION_ID = P_RECEIPT_ID
AND RCVT.TRANSACTION_TYPE = LV_RCV_TRANSACTION_TYPE
AND RCVT.SHIPMENT_HEADER_ID = RCVSH.SHIPMENT_HEADER_ID
AND RCVSH.RECEIPT_SOURCE_CODE = CP_RECEIPT_SOURCE_CODE
AND RCVSH.ORGANIZATION_ID = JHRU.ORGANIZATION_ID;
SELECT
DECODE('Y'
,JHRU.MANUFACTURING
,'Manufacturing'
,JHRU.TRADING
,'Dealer')
FROM
JAI_CMN_INVENTORY_ORGS JHRU
WHERE ORGANIZATION_ID = ABS(VENDOR_ID)
AND LOCATION_ID = ABS(VENDOR_SITE_ID);
SELECT
TRANSACTION_TYPE,
TRANSACTION_ID,
SHIPMENT_LINE_ID
FROM
RCV_TRANSACTIONS
WHERE TRANSACTION_ID = P_TRANSACTION_ID;
FOR qty_rec IN (SELECT
PRIMARY_QUANTITY
FROM
RCV_TRANSACTIONS
WHERE TRANSACTION_ID = LN_RECEIVE_TRX_ID
OR ( TRANSACTION_TYPE = LV_CRCT_TRANSACTION
AND PARENT_TRANSACTION_ID = LN_RECEIVE_TRX_ID )) LOOP
V_QTY := V_QTY + QTY_REC.PRIMARY_QUANTITY;
FOR qty_rec IN (SELECT
( CLOSING_BALANCE_QTY - OPENING_BALANCE_QTY ) QTY
FROM
JAI_CMN_RG_23AC_I_TRXS
WHERE REGISTER_ID_PART_II = CF_QTYFORMULA.REGISTER_ID) LOOP
V_QTY := QTY_REC.QTY;
SELECT
COUNT(1)
FROM
JAI_CMN_RG_23AC_II_TRXS A
WHERE A.ORGANIZATION_ID = P_ORGANIZATION_ID
AND A.LOCATION_ID = P_LOCATION_ID
AND REGISTER_TYPE = P_REGISTER_TYPE
AND A.INVENTORY_ITEM_ID <> 0
AND REGISTER_ID = P_REGISTER_ID
AND TRUNC(A.CREATION_DATE) BETWEEN NVL(P_TRN_FROM_DATE
,TRUNC(A.CREATION_DATE))
AND NVL(P_TRN_TO_DATE
,TRUNC(SYSDATE));
SELECT
NVL(SUM(CREDIT)
,0) - NVL(SUM(DEBIT)
,0)
FROM
JAI_CMN_RG_OTHERS
WHERE SOURCE_REGISTER_ID = CP_REG_ID
AND SOURCE_TYPE = 1
AND TAX_TYPE IN ( 'EXCISE_SH_EDU_CESS' , 'CVD_SH_EDU_CESS' );
SELECT
NVL(SUM(CREDIT)
,0) - NVL(SUM(DEBIT)
,0)
FROM
JAI_CMN_RG_OTHERS
WHERE SOURCE_REGISTER_ID = CP_REG_ID
AND SOURCE_TYPE = 1
AND TAX_TYPE IN ( LV_TAX_TYPE_EXC_EDU_CESS , LV_TAX_TYPE_CVD_EDU_CESS );
FOR rnd_rec IN (SELECT
REGISTER_ID,
NVL(CR_BASIC_ED
,0) - NVL(DR_BASIC_ED
,0) CENVAT_AMT,
NVL(CR_OTHER_ED
,0) - NVL(DR_OTHER_ED
,0) SED_AMT,
NVL(CR_ADDITIONAL_ED
,0) - NVL(DR_ADDITIONAL_ED
,0) ADDL_AMT,
NVL(CR_ADDITIONAL_CVD
,0) - NVL(DR_ADDITIONAL_CVD
,0) ADDLCVD_AMT
FROM
JAI_CMN_RG_23AC_II_TRXS
WHERE EXCISE_INVOICE_NO = cf_rounded_cenvat_amtformula.EXCISE_INVOICE_NO
AND EXCISE_INVOICE_DATE = cf_rounded_cenvat_amtformula.EXCISE_INVOICE_DATE
AND INVENTORY_ITEM_ID = 0
AND TRANSACTION_SOURCE_NUM = 18
AND REGISTER_TYPE = P_REGISTER_TYPE) LOOP
V_PARENT_INCLUDED_CNT := 0;
SELECT
SUM(NVL(CREDIT
,DEBIT))
FROM
JAI_CMN_RG_OTHERS
WHERE SOURCE_REGISTER_ID IN (
SELECT
RG23.REGISTER_ID
FROM
JAI_CMN_RG_23AC_II_TRXS RG23,
JAI_CMN_VENDOR_SITES VSITE,
MTL_SYSTEM_ITEMS MSI,
JAI_INV_ITM_SETUPS JA_MSI
WHERE RG23.REGISTER_TYPE = P_REGISTER_TYPE
AND RG23.OPENING_BALANCE < RG23.CLOSING_BALANCE
AND RG23.ORGANIZATION_ID = P_ORGANIZATION_ID
AND RG23.LOCATION_ID = P_LOCATION_ID
AND RG23.VENDOR_ID = vsite.vendor_id (+)
AND RG23.VENDOR_SITE_ID = vsite.vendor_site_id (+)
AND RG23.ORGANIZATION_ID = MSI.ORGANIZATION_ID
AND RG23.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID
AND RG23.ORGANIZATION_ID = JA_MSI.ORGANIZATION_ID
AND RG23.INVENTORY_ITEM_ID = JA_MSI.INVENTORY_ITEM_ID
AND TRUNC(RG23.CREATION_DATE) BETWEEN NVL(P_TRN_FROM_DATE
,TRUNC(RG23.CREATION_DATE))
AND NVL(P_TRN_TO_DATE
,TRUNC(SYSDATE))
AND NVL(RG23.INVENTORY_ITEM_ID
,-1) <> 0
AND NVL(RG23.ROUNDING_ID
,9999) <> - 1
AND RG23.EXCISE_INVOICE_NO = EXCISE_INVOICE_NO2
AND RG23.EXCISE_INVOICE_DATE = EXCISE_INVOICE_DATE2 )
AND SOURCE_REGISTER = LV_SOURCE_REGISTER_TYPE
AND TAX_TYPE IN ( LV_TAX_TYPE_EXC_EDU_CESS , LV_TAX_TYPE_CVD_EDU_CESS );
SELECT
SUM(NVL(CREDIT
,-DEBIT))
FROM
JAI_CMN_RG_OTHERS
WHERE SOURCE_REGISTER_ID IN (
SELECT
REGISTER_ID
FROM
JAI_CMN_RG_23AC_II_TRXS
WHERE EXCISE_INVOICE_NO = cf_sh_education_cessformula.EXCISE_INVOICE_NO
AND EXCISE_INVOICE_DATE = cf_sh_education_cessformula.EXCISE_INVOICE_DATE
AND REGISTER_TYPE = P_REGISTER_TYPE
AND ORGANIZATION_ID = P_ORGANIZATION_ID
AND LOCATION_ID = P_LOCATION_ID
AND TRUNC(CREATION_DATE) BETWEEN NVL(P_TRN_FROM_DATE
,TRUNC(CREATION_DATE))
AND NVL(P_TRN_TO_DATE
,TRUNC(SYSDATE)) )
AND SOURCE_REGISTER = DECODE(P_REGISTER_TYPE
,'A'
,'RG23A_P2'
,'C'
,'RG23C_P2')
AND TAX_TYPE IN ( 'EXCISE_SH_EDU_CESS' , 'CVD_SH_EDU_CESS' );