[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT hzcas.cust_acct_site_id
FROM hz_cust_site_uses_all hzcsu ,
hz_cust_acct_sites_all hzcas
WHERE hzcas.cust_acct_site_id = hzcsu.cust_acct_site_id
AND hzcsu.site_use_id = pn_site_use_id
AND hzcas.cust_account_id = pn_customer_id ;
SELECT vat_Reg_no
FROM JAI_CMN_CUS_ADDRESSES
WHERE customer_id = pn_customer_id
AND address_id = pn_address_id;
SELECT organization_id,location_id
FROM JAI_AR_TRXS
where customer_trx_id = pr_new.customer_trx_id;
SELECT TAX_INVOICE_NO
FROM JAI_AR_TRXS
WHERE Customer_Trx_Id = pr_new.customer_trx_id;
select 1
from JAI_AR_TRX_TAX_LINES
where link_to_cust_trx_line_id
in
(select customer_trx_line_id
from JAI_AR_TRX_LINES
where customer_trx_id = pr_new.customer_trx_id
)
and tax_id in
(select tax_id
from JAI_CMN_TAXES_ALL
where vat_flag = 'Y'
and org_id = pr_new.org_id
)
;
SELECT TYPE
FROM RA_CUST_TRX_TYPES_ALL
WHERE cust_trx_type_id = pr_new.cust_trx_type_id
AND NVL(org_id,0) = NVL(pr_new.org_id,0);
select vat_reg_no
from JAI_CMN_INVENTORY_ORGS
where organization_id = p_organization_id
and location_id = p_loc_id;
select current_number , jump_by , prefix
from JAI_CMN_INVENTORY_ORGS
where organization_id = p_organization_id
and location_id = p_loc_id;
select *
from jai_ar_trxs
where customer_trx_id = cn_customer_trx_id;
update JAI_CMN_INVENTORY_ORGS
set last_update_date = last_update_date
where vat_reg_no = v_vat_reg_no;
update JAI_AR_TRXS
set tax_invoice_no = v_vat_invoice_no
where customer_trx_id = pr_new.customer_trx_id;
JAI_AR_TRXS_PKG.UPDATE_EXCISE_REGISTERS(
pr_old => t_rec_old ,
pr_new => t_rec_new ,
pv_action => lv_action ,
pv_return_code => lv_return_code ,
pv_return_message => lv_return_message
);
update JAI_CMN_INVENTORY_ORGS
set current_number = NVL(v_vat_start_num,0) ,
prefix = v_vat_prefix,
jump_by = v_vat_jump_by
where vat_Reg_no = v_vat_reg_no;
REM NAME update_trx_number renamed from ARU_T2
REM
REM DESCRIPTION Called from trigger JAI_TAX_PROCESSING_PKG.PROCESS_COMPLETE
REM
REM NOTES Refers to old trigger JAI_AR_RCTA_TRIGGER_PKG.ARU_T2
REM
REM +======================================================================+
*/
PROCEDURE update_trx_number ( pr_old t_jai_rec%type , pr_new t_rec%type , pv_action varchar2 , pv_return_code out nocopy varchar2 , pv_return_message out nocopy varchar2 ) IS
v_trans_type Varchar2(30);
Select type
From RA_CUST_TRX_TYPES_ALL
Where cust_trx_type_id = pr_new.cust_trx_type_id
And NVL(org_id,0) = NVL(pr_new.org_id,0);
SELECT *
FROM
JAI_AR_TRX_LINES
WHERE customer_trx_line_id = pn_customer_trx_line_id;
select *
from jai_ar_trxs
where customer_trx_id = cn_customer_trx_id;
l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_TRX_NUMBER()';
Update JAI_AR_TRXS
Set Customer_Trx_ID = pr_new.Customer_Trx_ID
Where Customer_Trx_ID = pr_old.Customer_Trx_ID;
JAI_AR_TRXS_PKG.UPDATE_EXCISE_REGISTERS(
pr_old => t_rec_old ,
pr_new => t_rec_new ,
pv_action => lv_action ,
pv_return_code => lv_return_code ,
pv_return_message => lv_return_message
);
/*Update JAI_AR_TRX_LINES
Set Customer_Trx_Id = pr_new.Customer_Trx_ID
Where Customer_Trx_ID = pr_old.Customer_Trx_ID;*/
FOR c_jai_ar_trx_lines_rec IN (SELECT customer_trx_line_id
FROM JAI_AR_TRX_LINES
WHERE customer_trx_id = pr_old.customer_trx_id)
LOOP
OPEN c_jai_ar_trx_lines(c_jai_ar_trx_lines_rec.customer_trx_line_id);
Update JAI_AR_TRX_LINES
Set Customer_Trx_Id = pr_new.Customer_Trx_ID
Where Customer_Trx_Line_ID = c_jai_ar_trx_lines_rec.customer_trx_line_id;
Update JAI_AR_TRXS
Set Trx_Number = pr_new.Trx_Number
Where Customer_Trx_ID = pr_new.Customer_Trx_ID;
JAI_AR_TRXS_PKG.UPDATE_EXCISE_REGISTERS(
pr_old => t_rec_old ,
pr_new => t_rec_new ,
pv_action => lv_action ,
pv_return_code => lv_return_code ,
pv_return_message => lv_return_message
);
Update JAI_AR_TRXS
Set Trx_Number = pr_new.Trx_Number
Where Customer_Trx_ID = pr_new.Customer_Trx_ID;
Update JAI_AR_TRXS
Set Trx_Number = pr_new.Trx_Number
, Once_Completed_Flag = NVL(pr_new.Complete_Flag,'N')
Where Customer_Trx_ID = pr_new.Customer_Trx_ID;
Update JAI_AR_TRXS
Set Trx_Number = pr_new.Trx_Number
Where Customer_Trx_ID = pr_new.Customer_Trx_ID;
JAI_AR_TRXS_PKG.UPDATE_EXCISE_REGISTERS(
pr_old => t_rec_old ,
pr_new => t_rec_new ,
pv_action => lv_action ,
pv_return_code => lv_return_code ,
pv_return_message => lv_return_message
);
Pv_return_message := 'Encountered an error in JAI_AR_IMPORT_SYNC_PKG.UPDATE_TRX_NUMBER ' || substr(sqlerrm,1,1900);
END update_trx_number ;
REM NAME update_complete_flag renamed from ARU_T4
REM
REM DESCRIPTION Called from trigger JAI_TAX_PROCESSING_PKG.PROCESS_COMPLETE
REM
REM NOTES Refers to old trigger JAI_AR_RCTA_TRIGGER_PKG.ARU_T4
REM
REM +======================================================================+
*/
PROCEDURE update_complete_flag ( pr_old t_jai_rec%type , pr_new t_rec%type , pv_action varchar2 , pv_return_code out nocopy varchar2 , pv_return_message out nocopy varchar2 ) IS
v_org_id NUMBER;
v_update_rg VARCHAR2(1);
v_update_rg_flag VARCHAR2(1);
v_last_update_date DATE;
v_last_updated_by NUMBER;
v_last_update_login NUMBER;
v_update_rg23d_flag JAI_AR_TRXS.UPDATE_RG23D_FLAG%TYPE;
SELECT
organization_id ,
location_id ,
once_completed_flag ,
decode(once_completed_flag,'A','RG23A','C','RG23C','P','PLA') register_type,
update_rg_flag,
nvl(update_rg23d_flag,'N')
FROM
JAI_AR_TRXS
WHERE
customer_trx_id = v_customer_trx_id;
SELECT NVL(rg23a_balance,0) rg23a_balance ,NVL(rg23c_balance,0) rg23c_balance,NVL(pla_balance,0) pla_balance
FROM JAI_CMN_RG_BALANCES
WHERE organization_id = p_org_id AND location_id = p_loc_id;
SELECT register_code
FROM JAI_OM_OE_BOND_REG_HDRS
WHERE organization_id = p_org_id AND location_id = p_loc_id AND
register_id IN (SELECT register_id
FROM JAI_OM_OE_BOND_REG_DTLS
WHERE order_type_id = pr_new.batch_source_id AND order_flag= 'N');
SELECT MAX(A.fin_year)
FROM JAI_CMN_FIN_YEARS A
WHERE organization_id = p_org_id AND fin_active_flag = 'Y';
SELECT NVL(tax_amount,0) tax_amount
FROM JAI_AR_TRXS
WHERE customer_trx_id = v_customer_trx_id;
SELECT pref_rg23a , pref_rg23c , pref_pla
FROM JAI_CMN_INVENTORY_ORGS
WHERE organization_id = p_org_id AND
location_id = p_loc_id;
SELECT item_class, excise_flag
FROM JAI_INV_ITM_SETUPS
WHERE inventory_item_id = P_Item_Id AND
ORGANIZATION_ID = P_ORG_ID;
SELECT organization_id,location_id
FROM JAI_AR_TRXS
WHERE trx_number = v_parent_trx_number;
SELECT NVL(register_balance,0) register_balance
FROM JAI_OM_OE_BOND_TRXS
WHERE transaction_id = (SELECT MAX(A.transaction_id)
FROM JAI_OM_OE_BOND_TRXS A, JAI_OM_OE_BOND_REG_HDRS B
WHERE A.register_id = B.register_id
AND B.organization_id = p_org_id AND B.location_id = p_loc_id);
SELECT NVL(rg23d_register_balance,0) rg23d_register_balance
FROM JAI_OM_OE_BOND_TRXS
WHERE transaction_id = (SELECT MAX(A.transaction_id)
FROM JAI_OM_OE_BOND_TRXS A, JAI_OM_OE_BOND_REG_HDRS B
WHERE A.register_id = B.register_id
AND B.organization_id = p_org_id AND B.location_id = p_loc_id);
SELECT customer_trx_line_id, inventory_item_id, quantity,line_number,
excise_exempt_type, assessable_value
FROM JAI_AR_TRX_LINES
WHERE customer_trx_id = v_customer_trx_id
ORDER BY customer_trx_line_id;
SELECT SUM(quantity_applied)
FROM JAI_CMN_MATCH_RECEIPTS
WHERE ref_line_id = p_customer_trx_line_id;
SELECT
A.tax_id,
A.tax_rate t_rate,
A.tax_amount tax_amt,
A.func_tax_amount func_amt,
b.tax_type t_type,
b.stform_type,
A.tax_line_no
FROM JAI_AR_TRX_TAX_LINES A ,
JAI_CMN_TAXES_ALL B,
JAI_INV_ITM_SETUPS C
WHERE link_to_cust_trx_line_id = p_line_id
AND b.tax_type IN --('Excise','Addl. Excise','Other Excise')
(jai_constants.tax_type_excise,jai_constants.tax_type_exc_additional,jai_constants.tax_type_exc_other)
AND A.tax_id = b.tax_id
AND c.inventory_item_id = p_inventory_item_id
AND c.organization_id = p_org_id
--AND c.item_class IN ('RMIN','RMEX','CGEX','CGIN','CCEX','CCIN','FGIN','FGEX')
AND c.item_class IN ( jai_constants.item_class_rmin, jai_constants.item_class_rmex,
jai_constants.item_class_cgex, jai_constants.item_class_cgin,
jai_constants.item_class_ccex, jai_constants.item_class_ccin,
jai_constants.item_class_fgin, jai_constants.item_class_fgex
)
ORDER BY 1;
SELECT ssi_unit_flag, nvl(trading,'N')
FROM JAI_CMN_INVENTORY_ORGS
WHERE organization_id = p_org_id AND
location_id = p_loc_id;
SELECT TYPE
FROM RA_CUST_TRX_TYPES_ALL
WHERE cust_trx_type_id = pr_new.cust_trx_type_id
AND (org_id = pr_new.org_id
OR
(org_id is null and pr_new.org_id is null)) ;
SELECT name
FROM Ra_Batch_Sources_All
WHERE batch_source_id = pr_new.batch_source_id
AND (org_id = pr_new.org_id
OR
( org_id is null AND pr_new.org_id is null));
SELECT start_number, end_number, jump_by, prefix
FROM JAI_CMN_RG_EXC_INV_NOS
WHERE organization_id = p_organization_id
AND location_id = p_location_id
AND fin_year = p_fin_year
AND transaction_type IN ( 'I','DOM','EXP')
AND order_invoice_type = p_batch_name
AND register_code = p_register_code ;
SELECT NVL(MAX(GP1),0),NVL(MAX(GP2),0)
FROM JAI_CMN_RG_EXC_INV_NOS
WHERE organization_id = p_org_id
AND location_id = p_loc_id
AND fin_year = p_fin_year
AND transaction_type IS NULL
AND order_invoice_type IS NULL
AND register_code IS NULL;
SELECT meaning
FROM ja_lookups
WHERE lookup_code = p_register_code
AND lookup_type = cp_register_type; /*'JAI_REGISTER_TYPE'; Ramananda for removal of SQL LITERALs */
SELECT MODVAT_REVERSE_PERCENT
FROM JAI_CMN_INVENTORY_ORGS
WHERE organization_id = v_org_id
AND (location_id = v_location_id
OR
(location_id is NULL and v_location_id is NULL));
SELECT A.tax_rate
FROM JAI_AR_TRX_TAX_LINES A, JAI_CMN_TAXES_ALL b
WHERE A.tax_id = b.tax_id
AND A.link_to_cust_trx_line_id = p_cust_trx_line_id
AND b.tax_type = jai_constants.tax_type_modvat_recovery ; --'Modvat Recovery';
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);
SELECT nvl(sum(jrtl.func_tax_amount),0)
FROM JAI_AR_TRXS jtrx,
JAI_AR_TRX_LINES jtl,
JAI_AR_TRX_TAX_LINES jrtl,
JAI_CMN_TAXES_ALL jtc ,
JAI_INV_ITM_SETUPS jmtl
WHERE jrtl.tax_id = jtc.tax_id
AND jtrx.customer_trx_id = jtl.customer_Trx_id
AND jrtl.link_to_cust_trx_line_id = jtl.customer_trx_line_id
AND jtl.inventory_item_id = jmtl.inventory_item_id
AND jtrx.organization_id = jmtl.organization_id
--AND jmtl.item_class in ('RMIN','RMEX','CGEX','CGIN','FGIN','FGEX','CCIN','CCEX')
AND jmtl.item_class IN ( jai_constants.item_class_rmin, jai_constants.item_class_rmex,
jai_constants.item_class_cgex, jai_constants.item_class_cgin,
jai_constants.item_class_ccex, jai_constants.item_class_ccin,
jai_constants.item_class_fgin, jai_constants.item_class_fgex
)
AND jtc.tax_type like '%Excise%'
AND jtl.customer_trx_id = pr_new.customer_trx_id
AND jtrx.customer_trx_id = pr_new.customer_trx_id;
SELECT NVL(SUM(jrctl.func_tax_amount),0) tax_amount
FROM JAI_AR_TRX_TAX_LINES jrctl ,
JAI_CMN_TAXES_ALL jtc
WHERE jtc.tax_id = jrctl.tax_id
AND link_to_cust_trx_line_id IN
(SELECT customer_trx_line_id
FROM JAI_AR_TRX_LINES
WHERE customer_trx_id = pr_new.customer_trx_id
)
AND upper(jtc.tax_type) IN (upper(jai_constants.tax_type_cvd_edu_cess), upper(jai_constants.tax_type_exc_edu_cess));
SELECT '1'
FROM Fnd_Concurrent_Requests FCR
WHERE FCR.argument1 = to_char(cp_customer_trx_id)
and fcr.status_code<> 'C'
and fcr.phase_code<>'C'
AND fcr.request_id IN (Select nvl(Max(Request_ID),-1)
From Fnd_Concurrent_Programs FCP,
Fnd_Application FA ,
Fnd_Concurrent_Requests FCR
Where FCR.Program_Application_ID = FA.Application_ID
AND FCR.Concurrent_Program_ID = FCP.Concurrent_Program_ID
AND FA.Application_ID = FCP.Application_ID
AND Concurrent_Program_Name = 'JAILINEGL'
AND FA.Application_Short_Name = 'JA'
AND FCR.argument1 = To_Char(cp_customer_trx_id) );
SELECT
vat_invoice_no
FROM JAI_AR_TRXS
WHERE customer_trx_id = pr_new.customer_trx_id;
SELECT
regime_id ,
regime_code
FROM
JAI_AR_TRX_TAX_LINES jcttl,
JAI_AR_TRX_LINES jctl,
JAI_CMN_TAXES_ALL jtc ,
jai_regime_tax_types_v jrttv
WHERE
jcttl.link_to_cust_trx_line_id = jctl.customer_trx_line_id AND
jctl.customer_trx_id = pr_new.customer_trx_id AND
jcttl.tax_id = jtc.tax_id AND
jtc.tax_type = jrttv.tax_type AND
regime_code = jai_constants.vat_regime AND
jtc.org_id = pr_new.org_id ;
SELECT
1
FROM
JAI_AR_TRX_TAX_LINES jcttl,
JAI_AR_TRX_LINES jctl,
JAI_CMN_TAXES_ALL jtc
WHERE
jcttl.link_to_cust_trx_line_id = jctl.customer_trx_line_id AND
jctl.customer_trx_id = pr_new.customer_trx_id AND
jcttl.tax_id = jtc.tax_id AND
jtc.org_id = pr_new.org_id AND
jtc.tax_type = cp_tax_type ;
SELECT
regime_id
FROM
jai_regime_tax_types_v
WHERE
regime_code = jai_constants.vat_regime
AND rownum = 1 ;
SELECT
nvl(attribute_value ,'N') attribute_value
FROM
JAI_RGM_ORG_REGNS_V
WHERE
regime_code = jai_constants.vat_regime AND
attribute_type_code = jai_constants.regn_type_others AND /*'OTHERS' AND */
attribute_code = jai_constants.attr_code_same_inv_no AND /*'SAME_INVOICE_NO' AND */
organization_id = cp_organization_id AND
location_id = cp_location_id;
SELECT
excise_invoice_no
FROM
JAI_AR_TRX_LINES
WHERE
customer_trx_id = pr_new.customer_trx_id ;
SELECT
gl_date
FROM
ra_cust_trx_line_gl_dist_all
WHERE
customer_trx_id = pr_new.customer_trx_id AND
account_class = cp_account_class AND
latest_rec_flag = 'Y';
SELECT regime_id ,
regime_code
FROM
JAI_AR_TRX_TAX_LINES jcttl,
JAI_AR_TRX_LINES jctl,
JAI_CMN_TAXES_ALL jtc ,
jai_regime_tax_types_v jrttv
WHERE jcttl.link_to_cust_trx_line_id = jctl.customer_trx_line_id
AND jctl.customer_trx_id = pr_new.customer_trx_id
AND jcttl.tax_id = jtc.tax_id
AND jtc.tax_type = jrttv.tax_type
AND regime_code = cp_regime_code
AND jtc.org_id = pr_new.org_id ;
SELECT regime_id,
attribute_value
FROM JAI_RGM_ORG_REGNS_V orrg
WHERE orrg.organization_id = cp_organization_id
AND orrg.location_id = cp_location_id
AND attribute_type_code = jai_constants.rgm_attr_type_code_primary
AND attribute_code = jai_constants.attr_code_regn_no
AND regime_code = cp_regime_code;
SELECT *
FROM
JAI_AR_TRX_LINES
WHERE customer_trx_line_id = pn_customer_trx_line_id;
select *
from jai_ar_trxs
where customer_trx_id = cn_customer_trx_id;
l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_COMPLETE_FLAG()';
v_update_rg_flag := 'N';
v_last_update_date := pr_new.last_update_date;
v_last_updated_by := pr_new.last_updated_by;
v_last_update_login := pr_new.last_update_login;
FETCH Complete_Cur INTO v_org_id, v_loc_id,v_complete_flag,v_reg_type, v_update_rg_flag,v_update_rg23d_flag;
v_rg_flag := v_update_rg_flag;
UPDATE JAI_AR_TRXS
SET ONCE_COMPLETED_FLAG = pr_new.COMPLETE_FLAG
,complete_flag = pr_new.COMPLETE_FLAG --Added by Zhiwei for Trigger Elimination for Import AR
WHERE CUSTOMER_TRX_ID = V_CUSTOMER_TRX_ID;
JAI_AR_TRXS_PKG.UPDATE_EXCISE_REGISTERS(
pr_old => t_rec_old ,
pr_new => t_rec_new ,
pv_action => lv_action ,
pv_return_code => lv_return_code ,
pv_return_message => lv_return_message
);
UPDATE JAI_AR_TRXS
SET ONCE_COMPLETED_FLAG = pr_new.COMPLETE_FLAG
,complete_flag = pr_new.complete_flag --Added by Zhiwei for ZX integration
--,TRX_DATE = pr_new.trx_date --Added by Zhiwei for ZX integration
WHERE CUSTOMER_TRX_ID = V_CUSTOMER_TRX_ID;
JAI_AR_TRXS_PKG.UPDATE_EXCISE_REGISTERS(
pr_old => t_rec_old ,
pr_new => t_rec_new ,
pv_action => lv_action ,
pv_return_code => lv_return_code ,
pv_return_message => lv_return_message
);
v_update_rg_flag := 'Y';
(v_rg_flag = 'Y' OR v_update_rg_flag = 'Y') AND
v_reg_code IS NOT NULL
THEN
FOR Line_Rec IN Line_Cur LOOP
FOR excise_cal_rec IN excise_cal_cur(Line_Rec.customer_trx_line_id, Line_Rec.Inventory_Item_ID, v_org_id) LOOP
IF excise_cal_rec.t_type IN ('Excise') THEN
v_basic_ed := NVL(v_basic_ed,0) + NVL(excise_cal_rec.func_amt,0);
IF v_trading_flag = 'Y' AND v_update_rg23d_flag = 'Y'
THEN
if line_rec.inventory_item_id is not null then
OPEN matched_qty_cur(line_rec.customer_trx_line_id);
UPDATE JAI_AR_TRX_LINES
SET EXCISE_INVOICE_NO = v_invoice_no ,
EXCISE_INVOICE_DATE = SYSDATE
WHERE CUSTOMER_TRX_LINE_ID = LINE_REC.customer_trx_line_id AND
INVENTORY_ITEM_ID = LINE_REC.inventory_item_id AND
CUSTOMER_TRX_ID = v_customer_trx_id;
SELECT once_completed_flag
INTO v_reg_type
FROM JAI_AR_TRXS
WHERE CUSTOMER_TRX_ID = pr_new.Customer_trx_id;
UPDATE JAI_AR_TRX_LINES
SET PAYMENT_REGISTER = v_excise_paid_register
WHERE CUSTOMER_TRX_LINE_ID = LINE_REC.customer_trx_line_id AND
INVENTORY_ITEM_ID = LINE_REC.inventory_item_id AND
CUSTOMER_TRX_ID = v_customer_trx_id;
SELECT once_completed_flag
INTO v_reg_type1
FROM JAI_AR_TRXS
WHERE CUSTOMER_TRX_ID = pr_new.Customer_trx_id;
UPDATE JAI_AR_TRX_LINES
SET PAYMENT_REGISTER = v_excise_paid_register
WHERE CUSTOMER_TRX_LINE_ID = LINE_REC.customer_trx_line_id AND
INVENTORY_ITEM_ID = LINE_REC.inventory_item_id AND
CUSTOMER_TRX_ID = v_customer_trx_id;
INSERT INTO JAI_AR_TRX_INS_HDRS_T
(
ORGANIZATION_ID,
LOCATION_ID,
CUSTOMER_TRX_ID ,
SHIP_TO_CUSTOMER_ID,
SHIP_TO_SITE_USE_ID,
CUST_TRX_TYPE_ID,
TRX_DATE,
SOLD_TO_CUSTOMER_ID,
BATCH_SOURCE_ID,
BILL_TO_CUSTOMER_ID ,
BILL_TO_SITE_USE_ID ,
CREATED_BY ,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE
)
VALUES
(
V_ORG_ID,
V_LOC_ID,
V_CUSTOMER_TRX_ID ,
pr_new.SHIP_TO_CUSTOMER_ID,
pr_new.SHIP_TO_SITE_USE_ID,
pr_new.CUST_TRX_TYPE_ID,
pr_new.TRX_DATE,
pr_new.SOLD_TO_CUSTOMER_ID,
pr_new.BATCH_SOURCE_ID,
pr_new.BILL_TO_CUSTOMER_ID ,
pr_new.BILL_TO_SITE_USE_ID,
FND_GLOBAL.USER_ID ,
SYSDATE ,
FND_GLOBAL.USER_ID ,
SYSDATE);
UPDATE JAI_AR_TRXS
SET
ONCE_COMPLETED_FLAG = pr_new.COMPLETE_FLAG
WHERE CUSTOMER_TRX_ID = V_CUSTOMER_TRX_ID;
JAI_AR_TRXS_PKG.UPDATE_EXCISE_REGISTERS(
pr_old => t_rec_old ,
pr_new => t_rec_new ,
pv_action => lv_action ,
pv_return_code => lv_return_code ,
pv_return_message => lv_return_message
);
Pv_return_message := 'Encountered an error in JAI_AR_IMPORT_SYNC_PKG.UPDATE_COMPLETE_FLAG ' || substr(sqlerrm,1,1900);
END update_complete_flag ;
SELECT ONCE_COMPLETED_FLAG
FROM JAI_AR_TRXS
WHERE CUSTOMER_TRX_ID = V_HEADER_ID;
SELECT interface_line_attribute6 order_line_id
,inventory_item_id
,customer_trx_line_id
FROM ra_customer_trx_lines_all
WHERE customer_trx_id = pr_new.customer_trx_id;
SELECT count(1)
FROM OE_ORDER_HEADERS_ALL oh,
OE_ORDER_LINES_ALL ol,
OE_TRANSACTION_TYPES_TL ot,
oe_workflow_assignments owf
WHERE oh.header_id = ol.header_id
AND oh.order_type_id = ot.transaction_type_id
AND oh.order_type_id = owf.order_type_id
AND ol.line_type_id = owf.line_type_id
AND oh.order_number = pr_new.interface_header_attribute1
AND ot.language = userenv('LANG')
AND ol.line_id = pn_order_line_id
AND owf.process_name IN ('R_RMA_CREDIT_APP_HDR_INV',
'R_RMA_CREDIT_WO_SHIP_APPROVE',
'R_RMA_CREDIT_WO_SHIP_HDR_INV',
'R_RMA_FOR_CREDIT_WO_SHIPMENT',
'R_RMA_FOR_OTA_CREDIT');
SELECT COUNT(1)
FROM MTL_SYSTEM_ITEMS msi,
JAI_OM_OE_RMA_LINES l
WHERE msi.inventory_item_id = pn_inventory_item_id
AND msi.inventory_item_id = l.inventory_item_id
AND l.rma_line_id = pn_order_line_id
AND msi.shippable_item_flag = 'N' ;
SELECT organization_id ,
location_id ,
vat_invoice_no ,
vat_invoice_date
FROM JAI_AR_TRXS
WHERE customer_trx_id = pr_new.customer_trx_id;
SELECT type
FROM ra_cust_trx_types_all
WHERE cust_trx_type_id = pr_new.cust_trx_type_id AND
NVL(org_id,0) = NVL(pr_new.org_id,0);
SELECT regime_id ,
regime_code
FROM
JAI_AR_TRX_TAX_LINES jcttl,
JAI_AR_TRX_LINES jctl,
JAI_CMN_TAXES_ALL jtc ,
jai_regime_tax_types_v jrttv
WHERE jcttl.link_to_cust_trx_line_id = jctl.customer_trx_line_id
AND jctl.customer_trx_id = pr_new.customer_trx_id
AND jcttl.tax_id = jtc.tax_id
AND jtc.tax_type = jrttv.tax_type
AND regime_code = jai_constants.vat_regime
AND jtc.org_id = pr_new.org_id ;
SELECT gl_date
FROM ra_cust_trx_line_gl_dist_all
WHERE customer_trx_id = pr_new.customer_trx_id
AND account_class = cp_acct_class
AND latest_rec_flag = 'Y';
SELECT vat_invoice_no
FROM JAI_AR_TRXS
WHERE customer_trx_id = pr_new.previous_customer_trx_id;
SELECT 1
FROM JAI_AR_TRX_TAX_LINES jcttl,
JAI_AR_TRX_LINES jctl,
JAI_CMN_TAXES_ALL jtc
WHERE jcttl.link_to_cust_trx_line_id = jctl.customer_trx_line_id
AND jctl.customer_trx_id = pr_new.customer_trx_id
AND jcttl.tax_id = jtc.tax_id
AND jtc.org_id = pr_new.org_id
AND jtc.tax_type = cp_tax_type ;
SELECT regime_id
FROM jai_regime_tax_types_v
WHERE regime_code = jai_constants.vat_regime
AND rownum = 1 ;
SELECT jwl.vat_invoice_no
FROM OE_ORDER_HEADERS_ALL ohc,
oe_transaction_types_tl ot,
JAI_OM_WSH_LINES_ALL jwl
WHERE ohc.order_type_id = ot.transaction_type_id
AND ot.LANGUAGE = userenv('LANG')
AND ohc.source_document_id = jwl.ORDER_HEADER_ID
AND ohc.ORDER_NUMBER = pr_new.INTERFACE_HEADER_ATTRIBUTE1
AND ot.NAME = pr_new.INTERFACE_HEADER_ATTRIBUTE2;
SELECT
jctl.customer_trx_id ,
jctl.vat_assessable_value ,
nvl(jctl.vat_exemption_flag,'N') vat_exemption_flag ,
jcttl.customer_trx_line_id ,
jcttl.tax_id ,
jcttl.link_to_cust_trx_line_id ,
jcttl.func_tax_amount ,
jcttl.creation_date ,
jtc.tax_type ,
jcttl.tax_rate
FROM
JAI_AR_TRX_LINES jctl ,
JAI_AR_TRX_TAX_LINES jcttl ,
JAI_CMN_TAXES_ALL jtc ,
(
SELECT jrttv1.tax_type tax_type
FROM jai_regime_tax_types_v jrttv1
WHERE jrttv1.regime_code = jai_constants.vat_regime
UNION
SELECT 'VAT REVERSAL' tax_type
FROM DUAL
) jrttv
WHERE
jctl.customer_trx_id = pr_new.customer_trx_id AND
jctl.customer_trx_line_id = jcttl.link_to_cust_trx_line_id AND
jctl.customer_trx_line_id = cp_customer_trx_line_id AND
jcttl.tax_id = jtc.tax_id AND
jtc.tax_type = jrttv.tax_type AND
NOT EXISTS ( SELECT
1
FROM
jai_rgm_trx_records jrtr
WHERE
jrtr.source = cp_source AND
jrtr.source_trx_type = cp_source_trx_type AND
jrtr.organization_id = cp_organization_id AND
jrtr.location_id = cp_loc_id AND
jrtr.source_table_name = cp_source_table_name AND
jrtr.trx_reference1 = pr_new.customer_trx_id AND
jrtr.source_document_id = jcttl.customer_trx_line_id AND
jrtr.reference_id = jcttl.tax_id
) ;
jai_cmn_rgm_recording_pkg.insert_vat_repository_entry (
pn_repository_id => ln_repository_id ,
pn_regime_id => ln_regime_id ,
pv_tax_type => rec_cur_get_man_ar_inv_taxes.tax_type ,
pv_organization_type => jai_constants.orgn_type_io ,
pn_organization_id => v_organization_id ,
pn_location_id => v_loc_id ,
pv_source => jai_constants.source_ar ,
pv_source_trx_type => jai_constants.source_ttype_man_ar_inv ,
pv_source_table_name => jai_constants.tname_cus_trx_lines ,
pn_source_id => rec_cur_get_man_ar_inv_taxes.customer_trx_line_id ,
pd_transaction_date => rec_cur_get_man_ar_inv_taxes.creation_date ,
pv_account_name => lc_account_name , --Date 14/06/2007 by sacsethi for bug 6072461
pn_charge_account_id => ln_charge_ac_id ,
pn_balancing_account_id => ln_balancing_ac_id ,
pn_credit_amount => ln_credit_amount ,
pn_debit_amount => ln_debit_amount ,
pn_assessable_value => rec_cur_get_man_ar_inv_taxes.vat_assessable_value ,
pn_tax_rate => rec_cur_get_man_ar_inv_taxes.tax_rate ,
pn_reference_id => rec_cur_get_man_ar_inv_taxes.tax_id ,
pn_batch_id => NULL ,
pn_inv_organization_id => v_organization_id ,
pv_invoice_no => lv_vat_invoice_no ,
pd_invoice_date => nvl(nvl(ld_gl_date,pr_new.trx_date),rec_cur_get_man_ar_inv_taxes.creation_date) ,
pv_called_from => jai_constants.vat_repo_call_from_om_ar ,
pv_process_flag => lv_process_flag ,
pv_process_message => lv_process_message ,
pv_trx_reference_context => jai_constants.contxt_manual_ar ,
pv_trx_reference1 => rec_cur_get_man_ar_inv_taxes.customer_trx_id ,
pv_trx_reference2 => rec_cur_get_man_ar_inv_taxes.link_to_cust_trx_line_id ,
pv_trx_reference3 => NULL ,
pv_trx_reference4 => NULL ,
pv_trx_reference5 => NULL
);