[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT vendor_name supplier_name,
vendor_id supplier_id,
NVL(individual_1099,num_1099)||'-'||global_attribute12 taxpayer_id,
global_attribute8 simplif_regime_cont_type
FROM ap_suppliers
WHERE global_attribute1 = '06'
AND global_attribute8 IN ('GOODS','SERVICES')
AND (vendor_id = NVL(p_supplier_id,vendor_id));
SELECT ai.invoice_id invoice_id
,ai.invoice_num invoice_num
,ai.invoice_date invoice_date
,ai.payment_status_flag invoice_status
,ai.global_attribute13 dgi_type
,SUM(DECODE(ai.invoice_currency_code, 'ARS', aid.amount, aid.base_amount)) invoice_amt
FROM ap_invoices ai,
ap_invoice_lines ail,
ap_invoice_distributions aid
WHERE ai.vendor_id = p_supplier_id
AND ai.invoice_id = aid.invoice_id
AND ai.invoice_id = ail.invoice_id
AND ail.line_number = aid.invoice_line_number
AND ai.invoice_date BETWEEN p_from_date AND p_to_date
AND ail.line_type_lookup_code NOT IN ('AWT','TAX')
AND ai.invoice_type_lookup_code IN ('STANDARD','PREPAYMENT','CREDIT')
AND ai.payment_status_flag IN ('N','P','Y')
AND ai.cancelled_date IS NULL
AND ai.legal_entity_id = p_legal_entity_id
GROUP BY ai.invoice_id, ai.invoice_date, ai.invoice_num, ai.payment_status_flag, ai.global_attribute13
ORDER BY ai.invoice_date,ai.invoice_id;
v_supp_update_status VARCHAR2(1);
v_update_supp_appl VARCHAR2(1);
SELECT nvl(threshold_amt,0) INTO p_goods_supp_thld
FROM jl_ar_ap_mtbt_thresholds
WHERE contributor_type = 'GOODS'
AND p_report_date BETWEEN start_date AND nvl(end_date,add_months(sysdate,12*50));
SELECT nvl(threshold_amt,0) INTO p_service_supp_thld
FROM JL_AR_AP_MTBT_THRESHOLDS
WHERE contributor_type = 'SERVICES'
AND p_report_date BETWEEN start_date AND nvl(end_date,add_months(sysdate,12*50));
v_update_supp_appl := 'N';
SELECT max(invoice_date), trunc(add_months(max(invoice_date), -11),'MM')
INTO v_to_date, v_from_date
FROM AP_INVOICES
WHERE vendor_id = c_supp_rec(i_supp).supplier_id
AND cancelled_date IS NULL --BUG 9792829
AND invoice_date <= P_REPORT_DATE;
SELECT 1
INTO v_flag
FROM JL_ZZ_AP_AWT_TYPES awt,
JL_ZZ_AP_SUPP_AWT_TYPES swt
WHERE swt.vendor_id = c_supp_rec(i_supp).SUPPLIER_ID
AND swt.awt_type_code = awt.awt_type_code
AND awt.simplified_regime_flag = 'Y'
AND swt.wh_subject_flag = 'Y'
AND ROWNUM = 1;
SELECT DECODE(ai.invoice_currency_code,'ARS',ai.invoice_amount,ai.base_amount),
SUM(DECODE(ail.line_type_lookup_code,'TAX',(DECODE(ai.invoice_currency_code,'ARS', AID.amount, AID.base_amount)),0))
INTO v_inv_amt,
v_inv_tax_amt
FROM ap_invoices ai,
ap_invoice_lines ail,
ap_invoice_distributions aid
WHERE ai.invoice_id = AID.invoice_id
AND ai.invoice_id = ail.invoice_id
AND ail.line_number = aid.invoice_line_number
AND ai.invoice_id = c_supp_inv(inv_rec).invoice_id
GROUP BY DECODE(ai.invoice_currency_code,'ARS',ai.invoice_amount,ai.base_amount);
v_supp_update_status := 'N';
Insert_temp_data( c_supp_rec(i_supp).SUPPLIER_NAME,
c_supp_rec(i_supp).SUPPLIER_ID,
c_supp_rec(i_supp).TAXPAYER_ID,
c_supp_rec(i_supp).SIMPLIF_REGIME_CONT_TYPE,
v_supp_monotrib_status,
v_supp_update_status,
v_threshold_amt,
c_supp_inv(inv_rec).INVOICE_ID,
c_supp_inv(inv_rec).INVOICE_NUM,
c_supp_inv(inv_rec).INVOICE_DATE,
c_supp_inv(inv_rec).INVOICE_STATUS,
c_supp_inv(inv_rec).DGI_TYPE,
v_inv_amt,
v_inv_amt_without_tax,
v_threshold_Met );
v_supp_update_status := 'N';
Insert_temp_data( c_supp_rec(i_supp).SUPPLIER_NAME,
c_supp_rec(i_supp).SUPPLIER_ID,
c_supp_rec(i_supp).TAXPAYER_ID,
c_supp_rec(i_supp).SIMPLIF_REGIME_CONT_TYPE,
v_supp_monotrib_status,
v_supp_update_status,
v_threshold_amt,
c_supp_inv(inv_rec).INVOICE_ID,
c_supp_inv(inv_rec).INVOICE_NUM,
c_supp_inv(inv_rec).INVOICE_DATE,
c_supp_inv(inv_rec).INVOICE_STATUS,
c_supp_inv(inv_rec).DGI_TYPE,
v_inv_amt,
v_inv_amt_without_tax,
v_threshold_Met );
IF (v_update_supp_appl = 'N') THEN
--call of procedure to update the isupplier's applicability
Update_Supplier_Applicability (c_supp_rec(i_supp).SUPPLIER_ID, Applicability_Chngd_flag);
v_update_supp_appl := 'Y';
Update_Monotrib_Inv_Distrib_Wh(c_supp_inv(inv_rec).invoice_id, c_supp_rec(i_supp).SUPPLIER_ID);
FND_FILE.put_line( FND_FILE.LOG, 'INVOICE APPLICABILITY UPDATED SUCCESSFULLY FOR INVOICE ID : '|| c_supp_inv(inv_rec).invoice_id);
v_supp_update_status := 'Y';
Insert_temp_data( c_supp_rec(i_supp).SUPPLIER_NAME,
c_supp_rec(i_supp).SUPPLIER_ID,
c_supp_rec(i_supp).TAXPAYER_ID,
c_supp_rec(i_supp).SIMPLIF_REGIME_CONT_TYPE,
v_supp_monotrib_status,
v_supp_update_status,
v_threshold_amt,
c_supp_inv(inv_rec).INVOICE_ID,
c_supp_inv(inv_rec).INVOICE_NUM,
c_supp_inv(inv_rec).INVOICE_DATE,
c_supp_inv(inv_rec).INVOICE_STATUS,
c_supp_inv(inv_rec).DGI_TYPE,
v_inv_amt,
v_inv_amt_without_tax,
v_threshold_Met );
FND_FILE.put_line( FND_FILE.LOG, 'SUPPLIER APPLICABILITY UPDATED: Scenario where no unpaid Invoice Existed ');
v_supp_update_status := 'Y';
Insert_temp_data( c_supp_rec(i_supp).SUPPLIER_NAME,
c_supp_rec(i_supp).SUPPLIER_ID,
c_supp_rec(i_supp).TAXPAYER_ID,
c_supp_rec(i_supp).SIMPLIF_REGIME_CONT_TYPE,
v_supp_monotrib_status,
v_supp_update_status,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL );
v_supp_update_status := 'N';
Insert_temp_data( c_supp_rec(i_supp).SUPPLIER_NAME,
c_supp_rec(i_supp).SUPPLIER_ID,
c_supp_rec(i_supp).TAXPAYER_ID,
c_supp_rec(i_supp).SIMPLIF_REGIME_CONT_TYPE,
v_supp_monotrib_status,
v_supp_update_status,
v_threshold_amt,
c_supp_inv(inv_rec).INVOICE_ID,
c_supp_inv(inv_rec).INVOICE_NUM,
c_supp_inv(inv_rec).INVOICE_DATE,
c_supp_inv(inv_rec).INVOICE_STATUS,
c_supp_inv(inv_rec).DGI_TYPE,
v_inv_amt,
v_inv_amt_without_tax,
v_threshold_Met );
Insert_temp_data(c_supp_rec(i_supp).SUPPLIER_NAME,
c_supp_rec(i_supp).SUPPLIER_ID,
c_supp_rec(i_supp).TAXPAYER_ID,
c_supp_rec(i_supp).SIMPLIF_REGIME_CONT_TYPE,
v_supp_monotrib_status,
'N',
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL );
PROCEDURE Insert_temp_data(
P_SUPPLIER_NAME IN VARCHAR2,
P_SUPPLIER_ID IN NUMBER,
P_TAXPAYER_ID IN VARCHAR2,
P_SIMPLIF_REGIME_CONT_TYPE IN VARCHAR2,
P_supp_monotrib_status IN VARCHAR2,
P_supp_update_status IN VARCHAR2,
P_threshold_amt IN NUMBER,
P_INVOICE_ID IN NUMBER,
P_INVOICE_NUM IN VARCHAR2,
P_INVOICE_DATE IN DATE,
P_INVOICE_STATUS IN VARCHAR2,
P_DGI_TYPE IN VARCHAR2,
P_INV_AMOUNT IN NUMBER,
P_INV_AMT_WOUT_TAX IN NUMBER,
P_threshold_Met IN VARCHAR2
)
IS
BEGIN
INSERT
INTO JL_ZZ_INFO_T
(/*Supplier details*/
JL_INFO_V1
,JL_INFO_N1
,JL_INFO_V2
,JL_INFO_V3
,JL_INFO_V4
,JL_INFO_V5
,JL_INFO_N6
/*Invoice Details*/
,JL_INFO_N3
,JL_INFO_V6
,JL_INFO_D1
,JL_INFO_V7
,JL_INFO_V8
,JL_INFO_N4
,JL_INFO_N5
,JL_INFO_V9)
VALUES (/*Supplier details*/
P_SUPPLIER_NAME,
P_SUPPLIER_ID,
P_TAXPAYER_ID,
P_SIMPLIF_REGIME_CONT_TYPE,
P_supp_monotrib_status,
P_supp_update_status,
P_threshold_amt,
/*Invoice Details*/
P_INVOICE_ID,
P_INVOICE_NUM,
P_INVOICE_DATE,
P_INVOICE_STATUS,
P_DGI_TYPE,
P_INV_AMOUNT,
P_INV_AMT_WOUT_TAX,
P_threshold_Met);
FND_FILE.put_line( FND_FILE.LOG,'AN ERROR IS ENCOUNTERED WHILE INSERTING INTO TEMP TABLE '|| SQLCODE || 'ERROR ' || SQLERRM);
END Insert_temp_data;
PROCEDURE Update_Supplier_Applicability( P_Supplier_Id IN po_vendors.vendor_id%Type,
Applicability_Chngd_flag OUT NOCOPY VARCHAR2)
IS
CURSOR awt_types IS
SELECT awt_type_code, description, supplier_exempt_level, multilat_contrib_flag
FROM JL_ZZ_AP_AWT_TYPES
WHERE Simplified_Regime_Flag = 'Y'
AND nvl(start_date_active, sysdate) <= sysdate
AND nvl(end_date_active, sysdate) >= sysdate;
v_last_update_by NUMBER;
v_last_update_login NUMBER;
v_last_update_by := FND_GLOBAL.User_ID;
v_last_update_login := FND_GLOBAL.Login_Id;
FND_FILE.put_line( FND_FILE.LOG, 'INSIDE SUPPLIER APPLICABILITY UPDATE ROUTINE FOR SUPPLIER ID : '|| P_Supplier_Id);
SELECT tax_id, name
INTO v_tax_id, v_tax_name
FROM AP_TAX_CODES
WHERE global_attribute4 = c_rec.awt_type_code
AND inactive_date IS NULL
AND creation_date >= ( SELECT max(creation_date)
FROM AP_TAX_CODES --bug 14274573
WHERE global_attribute4 = c_rec.awt_type_code
AND inactive_date IS NULL
)
AND ROWNUM = 1;
SELECT count(*)
INTO v_flag
FROM JL_ZZ_AP_SUPP_AWT_TYPES
WHERE awt_type_code = c_rec.awt_type_code
AND vendor_id = P_Supplier_Id;
SELECT jl_zz_ap_supp_awt_types_s.nextval
INTO v_supp_awt_type_id
FROM dual;
SELECT jl_zz_ap_sup_awt_cd_s.nextval
INTO v_supp_awt_code_id
FROM dual;
---- Inserting into Supplier Applicability table
INSERT INTO JL_ZZ_AP_SUPP_AWT_TYPES (SUPP_AWT_TYPE_ID,
VENDOR_ID,
AWT_TYPE_CODE,
WH_SUBJECT_FLAG,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN)
VALUES (v_supp_awt_type_id,
P_Supplier_Id,
c_rec.awt_type_code,
'Y',
v_last_update_by,
sysdate,
v_last_update_by,
sysdate,
v_last_update_login
);
INSERT INTO JL_ZZ_AP_SUP_AWT_CD_ALL (SUPP_AWT_CODE_ID,
SUPP_AWT_TYPE_ID,
TAX_ID,
PRIMARY_TAX_FLAG,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
EFFECTIVE_START_DATE,
ORG_ID)
VALUES (v_supp_awt_code_id,
v_supp_awt_type_id,
v_tax_id,
'Y',
v_last_update_by,
sysdate,
v_last_update_by,
sysdate,
v_last_update_login,
TO_DATE('01/01/1950','DD/MM/YYYY'),
v_org_id
);
FND_FILE.put_line( FND_FILE.LOG, 'SUPPLIER APPLICABILITY UPDATED SUCCESSFULLY FOR SUPPLIER ID : '|| P_Supplier_Id);
UPDATE JL_ZZ_AP_SUPP_AWT_TYPES SET WH_SUBJECT_FLAG = 'Y'
WHERE awt_type_code = c_rec.awt_type_code
AND vendor_id = P_Supplier_Id;
SELECT SUPP_AWT_TYPE_ID INTO v_temp1
FROM JL_ZZ_AP_SUPP_AWT_TYPES
WHERE awt_type_code = c_rec.awt_type_code
AND vendor_id = P_Supplier_Id
AND ROWNUM = 1;
UPDATE JL_ZZ_AP_SUP_AWT_CD SET PRIMARY_TAX_FLAG = 'N'
WHERE SUPP_AWT_TYPE_ID = v_temp1;
SELECT count(*) INTO v_temp2
FROM JL_ZZ_AP_SUP_AWT_CD
WHERE TAX_ID = v_tax_id
AND SUPP_AWT_TYPE_ID = v_temp1;
UPDATE JL_ZZ_AP_SUP_AWT_CD SET PRIMARY_TAX_FLAG = 'Y'
WHERE TAX_ID = v_tax_id
AND SUPP_AWT_TYPE_ID = v_temp1;
SELECT jl_zz_ap_sup_awt_cd_s.nextval
INTO v_supp_awt_code_id
FROM dual;
INSERT INTO JL_ZZ_AP_SUP_AWT_CD_ALL(SUPP_AWT_CODE_ID,
SUPP_AWT_TYPE_ID,
TAX_ID,
PRIMARY_TAX_FLAG,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
EFFECTIVE_START_DATE,
ORG_ID)
VALUES (v_supp_awt_code_id,
v_temp1,
v_tax_id,
'Y',
v_last_update_by,
sysdate,
v_last_update_by,
sysdate,
v_last_update_login,
TO_DATE('01/01/1950','DD/MM/YYYY'),
v_org_id
);
FND_FILE.put_line( FND_FILE.LOG, 'SUPPLIER APPLICABILITY UPDATED SUCCESSFULLY FOR SUPPLIER ID : '|| P_Supplier_Id);
END Update_Supplier_Applicability;
PROCEDURE Update_Monotrib_Inv_Distrib_Wh
( P_Invoice_Id IN ap_invoices_all.invoice_id%TYPE
, P_vendor_id IN po_vendors.vendor_id%Type
--, P_Defaulting_flag BOOLEAN
) IS
CURSOR Invoice_Distrib IS
SELECT invoice_distribution_id
FROM ap_invoice_distributions
WHERE invoice_id = P_Invoice_ID;
FND_FILE.put_line( FND_FILE.LOG, 'INSIDE MONOTRIBUTO UNPAID INVOICE UPDATE PROCEDURE : '|| p_vendor_id ||'-'|| p_invoice_id);
SELECT apid.global_attribute2 -- Taxpayer Id for Colombia
,apid.global_attribute3 -- Ship to Location Argentina
,apid.line_type_lookup_code -- Line Type
INTO v_tax_payer_id,
v_ship_to_loc,
v_line_type
FROM AP_Invoice_Distributions apid,
AP_Invoice_Lines apil
WHERE apid.invoice_id = P_Invoice_Id
AND apid.invoice_distribution_id = db_reg.invoice_distribution_id
AND apil.line_number = apid.invoice_line_number
AND apid.invoice_id = apil.invoice_id;
END Update_Monotrib_Inv_Distrib_Wh;
SELECT swt.supp_awt_type_id ,
swt.awt_type_code,
swc.supp_awt_code_id,
swc.org_id,
tca.tax_id,
tca.global_attribute7, -- Zone
awt.jurisdiction_type,
awt.province_code,
awt.city_code
FROM jl_zz_ap_supp_awt_types swt,
jl_zz_ap_sup_awt_cd swc,
ap_tax_codes tca,
jl_zz_ap_awt_types awt
WHERE swt.vendor_id = C_vendor_id -- Select only for this Supplier
AND swt.wh_subject_flag = 'Y' -- Supp subject to the withholding tax type
AND swc.supp_awt_type_id = swt.supp_awt_type_id -- Join
AND swc.tax_id = tca.tax_id -- Join
AND (tca.inactive_date > sysdate -- Verify Tax Name Inactive Date
OR tca.inactive_date IS NULL)
AND swc.primary_tax_flag = 'Y' -- Verify the Primary Withholding Tax
AND awt.Simplified_Regime_Flag = 'Y' -- Verify the Simplified Regime Withholding tax Type ONLY
AND awt.awt_type_code = swt.awt_type_code -- Join
AND sysdate between nvl(swc.effective_start_date,sysdate) and nvl(swc.effective_end_date,sysdate)
; -- New Argentine AWT ER 6624809
FND_FILE.put_line( FND_FILE.LOG, 'INSIDE "Monotrib_Wh_Def_Line" PROCEDURE - PROVINCIAL Before Insert');
JL_ZZ_AP_AWT_DEFAULT_PKG.Insert_AWT_Default
(p_Invoice_Id
, p_inv_dist_id
, db_reg. supp_awt_code_id
, p_calling_sequence
, db_reg.org_id );
FND_FILE.put_line( FND_FILE.LOG, 'INSIDE "Monotrib_Wh_Def_Line" PROCEDURE - PROVINCIAL Before Insert');
JL_ZZ_AP_AWT_DEFAULT_PKG.Insert_AWT_Default
(p_Invoice_Id
, p_inv_dist_id
, db_reg. supp_awt_code_id
, p_calling_sequence
, db_reg.org_id );
FND_FILE.put_line( FND_FILE.LOG, 'INSIDE "Monotrib_Wh_Def_Line" PROCEDURE - ZONAL Before Insert');
JL_ZZ_AP_AWT_DEFAULT_PKG.Insert_AWT_Default
(p_Invoice_Id
, p_inv_dist_id
, db_reg. supp_awt_code_id
, p_calling_sequence
, db_reg.org_id );
FND_FILE.put_line( FND_FILE.LOG, 'INSIDE "Monotrib_Wh_Def_Line" PROCEDURE - MUNICIPAL Before Insert');
JL_ZZ_AP_AWT_DEFAULT_PKG.Insert_AWT_Default
(p_Invoice_Id
, p_inv_dist_id
, db_reg. supp_awt_code_id
, p_calling_sequence
, db_reg.org_id );
JL_ZZ_AP_AWT_DEFAULT_PKG.Insert_AWT_Default
(p_Invoice_Id
, p_inv_dist_id
, db_reg. supp_awt_code_id
, p_calling_sequence
, db_reg.org_id );
DELETE from JL_ZZ_INFO_T;