[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT boe_id, tax_category_id FROM JAI_BOE_MIGRATION_TEMP;
SELECT BOE_AMOUNT
,AMOUNT_APPLIED
,AMOUNT_WRITTEN_OFF
,INVOICE_NUM,
CONSOLIDATED_FLAG
FROM jai_cmn_boe_hdrs_all
WHERE boe_id = pn_boe_id;
update_boe_hdr(pn_boe_id
,pn_org_id
,ln_applied_amount
,lv_boe_invoice_num);
insert_boe_rounding(pn_boe_id);
PROCEDURE update_boe_hdr
(
pn_boe_id IN NUMBER
,pn_org_id IN NUMBER
,pn_amount_applied IN NUMBER
,pv_invoice_num IN VARCHAR2
) IS
CURSOR boe_invoice_cur(pn_invoice_num IN VARCHAR2) IS
SELECT PAYMENT_STATUS_FLAG
FROM AP_INVOICES
WHERE INVOICE_NUM = pn_invoice_num;
Fnd_File.put_line(Fnd_File.LOG,' **Start update_boe_hdr for boe_id:' || pn_boe_id ||' invoice num:' || pv_invoice_num ||'applied amount:' || pn_amount_applied || ' **');
UPDATE jai_cmn_boe_hdrs_all SET
status = boe_status,
migration_flag = 'Y',
org_id = pn_org_id,
BOE_WITHOUT_REFER_FLAG = decode(CONSOLIDATED_FLAG,'Y',NULL,'N','Y'),
CUSTOMS_AUTHORITY_ID = VENDOR_ID,
CUSTOMS_AUTHORITY_SITE_ID = VENDOR_SITE_ID,
BOE_DATE = IMPORT_DATE,
last_update_date = SYSDATE,
last_updated_by = fnd_global.user_id
WHERE boe_id = pn_boe_id;
Fnd_File.put_line(Fnd_File.LOG,' **Finish update_boe_hdr for boe_id:' || pn_boe_id ||' with boe status:' || boe_status || '--- **');
END update_boe_hdr;
SELECT BOE_ID,
SUM(BASIC_DUTY_AMOUNT) customs,
SUM(ADDL_DUTY_AMOUNT) additional_customs_duty,
SUM(SURCH_DUTY_AMOUNT) surcharge_duty,
SUM(TOTAL_DUTY_AMOUNT) total_tax_amount,
SUM(EDU_CESS_CVD_AMOUNT) cvd_education_cess,
SUM(EDU_CESS_CUSTOMS_AMOUNT) customs_edu_cess,
SUM(ADDITIONAL_CVD_AMT) additional_CVD,
SUM(SH_CUSTOMS_CESS_AMOUNT) customs_sh_education_cess,
SUM(SH_CVD_CESS_AMOUNT) cvd_sh_edu_cess
FROM jai_cmn_boe_dtls
WHERE BOE_ID = pn_boe_id
GROUP BY BOE_ID;
SELECT customs_tariff,duty_code,PKG_NUMBER,SERIAL_NUMBER
FROM jai_cmn_boe_dtls
WHERE BOE_ID = pn_boe_id AND rownum = 1;
SELECT jai_boe_details_s.NEXTVAL INTO ln_boe_detail_id FROM dual;
insert_boe_detail(p_boe_detail_rec => l_boe_detail_type_rec);
SELECT BOE_ID, item_number, SUM(QUANTITY) QUANTITY,
SUM(BASIC_DUTY_AMOUNT) customs,
SUM(ADDL_DUTY_AMOUNT) additional_customs_duty,
SUM(SURCH_DUTY_AMOUNT) surcharge_duty,
SUM(TOTAL_DUTY_AMOUNT) total_tax_amount,
SUM(EDU_CESS_CVD_AMOUNT) cvd_education_cess,
SUM(EDU_CESS_CUSTOMS_AMOUNT) customs_edu_cess,
SUM(ADDITIONAL_CVD_AMT) additional_CVD,
SUM(SH_CUSTOMS_CESS_AMOUNT) customs_sh_education_cess,
SUM(SH_CVD_CESS_AMOUNT) cvd_sh_edu_cess
FROM jai_cmn_boe_dtls
WHERE BOE_ID = v_boe_id
GROUP BY BOE_ID, item_number;
SELECT uom_code,customs_tariff,duty_code,PKG_NUMBER,SERIAL_NUMBER
FROM jai_cmn_boe_dtls
WHERE BOE_ID = pn_boe_id AND item_number = pn_item_number AND rownum = 1;
SELECT jai_boe_details_s.NEXTVAL INTO ln_boe_detail_id FROM dual;
insert_boe_detail(p_boe_detail_rec => l_boe_detail_type_rec);
PROCEDURE insert_boe_detail(p_boe_detail_rec IN JAI_BOE_DETAILS%ROWTYPE) IS
BEGIN
Fnd_File.put_line(Fnd_File.LOG,' **Start insert_boe_detail for boe_detail_id:' ||p_boe_detail_rec.BOE_DETAIL_ID || '--- **');
INSERT INTO JAI_BOE_DETAILS
(BOE_DETAIL_ID,
DETAIL_LINE_NUM,
BOE_ID, QUANTITY,
UOM_CODE,
INVENTORY_ITEM_ID,
CUSTOMS_TARIFF_CODE,
DUTY_CODE,
DESCRIPTION,
TAX_CATEGORY_ID,
TAX_AMOUNT,
PKG_MARKS_NUM,
PKG_SERIAL_NUMBER,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
OBJECT_VERSION_NUMBER)
VALUES
(p_boe_detail_rec.BOE_DETAIL_ID,
p_boe_detail_rec.DETAIL_LINE_NUM,
p_boe_detail_rec.BOE_ID,
p_boe_detail_rec.QUANTITY,
p_boe_detail_rec.UOM_CODE,
p_boe_detail_rec.INVENTORY_ITEM_ID,
p_boe_detail_rec.CUSTOMS_TARIFF_CODE,
p_boe_detail_rec.DUTY_CODE,
p_boe_detail_rec.DESCRIPTION,
p_boe_detail_rec.TAX_CATEGORY_ID,
round(p_boe_detail_rec.TAX_AMOUNT,2),
p_boe_detail_rec.PKG_MARKS_NUM,
p_boe_detail_rec.PKG_SERIAL_NUMBER,
SYSDATE,
fnd_global.user_id,
SYSDATE,
fnd_global.user_id,
fnd_global.user_id,
1);
END insert_boe_detail;
PROCEDURE insert_boe_detail_tax(p_boe_detail_tax_rec IN JAI_BOE_DETAIL_TAXES%ROWTYPE) IS
BEGIN
Fnd_File.put_line(Fnd_File.LOG,'**Start insert_boe_detail_tax for BOE_DETAIL_TAX_ID:'||p_boe_detail_tax_rec.BOE_DETAIL_TAX_ID || '--- **');
INSERT INTO JAI_BOE_DETAIL_TAXES
(BOE_DETAIL_TAX_ID,
BOE_DETAIL_ID,
TAX_LINE_NO,
TAX_ID,
TAX_TYPE,
TAX_AMOUNT,
APPLIED_AMOUNT,
INCLUSIVE_TAX_FLAG,
BOE_FLAG,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
OBJECT_VERSION_NUMBER)
VALUES
(p_boe_detail_tax_rec.BOE_DETAIL_TAX_ID,
p_boe_detail_tax_rec.BOE_DETAIL_ID,
p_boe_detail_tax_rec.TAX_LINE_NO,
p_boe_detail_tax_rec.TAX_ID,
p_boe_detail_tax_rec.TAX_TYPE,
round(p_boe_detail_tax_rec.TAX_AMOUNT,2),
round(p_boe_detail_tax_rec.APPLIED_AMOUNT,2),
'N',
'Y',
SYSDATE,
fnd_global.user_id,
SYSDATE,
fnd_global.user_id,
fnd_global.user_id,
1);
END insert_boe_detail_tax;
PROCEDURE insert_boe_rounding(pn_boe_id IN NUMBER) IS
l_boe_rounding_rec JAI_BOE_ROUNDINGS%ROWTYPE;
SELECT
jbt.tax_id,
SUM(jbt.tax_amount) tax_amount
FROM jai_boe_details jbd,
jai_boe_detail_taxes jbt
WHERE jbd.boe_detail_id = jbt.boe_detail_id
AND jbd.boe_id = pn_boe_id
AND jbt.boe_flag = 'Y'
GROUP BY jbt.tax_id;
Fnd_File.put_line(Fnd_File.LOG,' **Start insert_boe_rounding for boe_id:' ||pn_boe_id || '--- **');
SELECT jai_boe_roundings_s.NEXTVAL INTO ln_boe_rounding_id FROM dual;
INSERT INTO JAI_BOE_ROUNDINGS
(ROUNDING_ID,
BOE_ID,
TAX_ID,
TAX_AMOUNT,
ROUNDING_AMOUNT,
TOTAL_AMOUNT,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
OBJECT_VERSION_NUMBER)
VALUES
(l_boe_rounding_rec.rounding_id,
l_boe_rounding_rec.boe_id,
l_boe_rounding_rec.tax_id,
l_boe_rounding_rec.tax_amount,
l_boe_rounding_rec.rounding_amount,
l_boe_rounding_rec.total_amount,
SYSDATE,
fnd_global.user_id,
SYSDATE,
fnd_global.user_id,
fnd_global.user_id,
1);
END insert_boe_rounding;
SELECT count(b.tax_id) cnt_tax
FROM jai_cmn_tax_ctg_lines jctl,
jai_cmn_taxes_all b
WHERE jctl.tax_category_id = v_tax_category_id
AND jctl.tax_id = b.tax_id
AND b.tax_type = v_tax_type;
SELECT b.tax_id,
b.tax_name
FROM jai_cmn_tax_ctg_lines jctl,
jai_cmn_taxes_all b
WHERE jctl.tax_category_id = v_tax_category_id
AND jctl.tax_id = b.tax_id
AND b.tax_type = v_tax_type;
PROCEDURE insert_tax_wise_data
(
pn_tax_category_id IN NUMBER
,pv_tax_type IN VARCHAR2
,pn_boe_detail_id IN NUMBER
,pn_boe_tax_line_num IN NUMBER
,pn_tax_amount IN NUMBER
,pn_tax_applied_amount IN NUMBER
) IS
ln_tax_id NUMBER;
Fnd_File.put_line(Fnd_File.LOG,' **Start insert_tax_wise_data for boe_detail_id:' ||pn_boe_detail_id || '--- **');
SELECT jai_boe_detail_taxes_s.NEXTVAL INTO ln_boe_tax_id FROM dual;
insert_boe_detail_tax(p_boe_detail_tax_rec => l_boe_tax_type_rec);
END insert_tax_wise_data;
insert_tax_wise_data(pn_tax_category_id => pn_tax_category_id
,pv_tax_type => jai_constants.tax_type_customs
,pn_boe_detail_id => pn_boe_detail_id
,pn_boe_tax_line_num => ln_boe_tax_line_num
,pn_tax_amount => dedicate_dtl_rec.customs
,pn_tax_applied_amount => dedicate_dtl_rec.customs * nvl(pn_applied_percentage,0));
insert_tax_wise_data(pn_tax_category_id => pn_tax_category_id
,pv_tax_type => jai_constants.tax_type_boe_add_customs
,pn_boe_detail_id => pn_boe_detail_id
,pn_boe_tax_line_num => ln_boe_tax_line_num
,pn_tax_amount => dedicate_dtl_rec.additional_customs_duty
,pn_tax_applied_amount => dedicate_dtl_rec.additional_customs_duty * nvl(pn_applied_percentage,0));
insert_tax_wise_data(pn_tax_category_id => pn_tax_category_id
,pv_tax_type => jai_constants.tax_type_boe_surcharge_duty
,pn_boe_detail_id => pn_boe_detail_id
,pn_boe_tax_line_num => ln_boe_tax_line_num
,pn_tax_amount => dedicate_dtl_rec.surcharge_duty
,pn_tax_applied_amount => dedicate_dtl_rec.surcharge_duty * nvl(pn_applied_percentage,0));
insert_tax_wise_data(pn_tax_category_id => pn_tax_category_id
,pv_tax_type => jai_constants.tax_type_cvd_edu_cess
,pn_boe_detail_id => pn_boe_detail_id
,pn_boe_tax_line_num => ln_boe_tax_line_num
,pn_tax_amount => dedicate_dtl_rec.cvd_education_cess
,pn_tax_applied_amount => dedicate_dtl_rec.cvd_education_cess * nvl(pn_applied_percentage,0));
insert_tax_wise_data(pn_tax_category_id => pn_tax_category_id
,pv_tax_type => jai_constants.tax_type_customs_edu_cess
,pn_boe_detail_id => pn_boe_detail_id
,pn_boe_tax_line_num => ln_boe_tax_line_num
,pn_tax_amount => dedicate_dtl_rec.customs_edu_cess
,pn_tax_applied_amount => dedicate_dtl_rec.customs_edu_cess * nvl(pn_applied_percentage ,0));
insert_tax_wise_data(pn_tax_category_id => pn_tax_category_id
,pv_tax_type => jai_constants.tax_type_add_cvd
,pn_boe_detail_id => pn_boe_detail_id
,pn_boe_tax_line_num => ln_boe_tax_line_num
,pn_tax_amount => dedicate_dtl_rec.additional_CVD
,pn_tax_applied_amount => dedicate_dtl_rec.additional_CVD * nvl(pn_applied_percentage ,0));
insert_tax_wise_data(pn_tax_category_id => pn_tax_category_id
,pv_tax_type => jai_constants.tax_type_sh_customs_edu_Cess
,pn_boe_detail_id => pn_boe_detail_id
,pn_boe_tax_line_num => ln_boe_tax_line_num
,pn_tax_amount => dedicate_dtl_rec.customs_sh_education_cess
,pn_tax_applied_amount => dedicate_dtl_rec.customs_sh_education_cess * nvl(pn_applied_percentage ,0));
insert_tax_wise_data(pn_tax_category_id => pn_tax_category_id
,pv_tax_type => jai_constants.tax_type_sh_cvd_edu_cess
,pn_boe_detail_id => pn_boe_detail_id
,pn_boe_tax_line_num => ln_boe_tax_line_num
,pn_tax_amount => dedicate_dtl_rec.cvd_sh_edu_cess
,pn_tax_applied_amount => dedicate_dtl_rec.cvd_sh_edu_cess * nvl(pn_applied_percentage,0));
SELECT hdr.boe_id,
hdr.BOE_AMOUNT,
hdr.AMOUNT_APPLIED,
hdr.AMOUNT_WRITTEN_OFF,
hdr.status,
hdr.migration_flag,
OU.ORGANIZATION_ID OU_ID
FROM JAI_CMN_BOE_HDRS_ALL hdr,
org_organization_definitions INV,
hr_all_organization_units OU
WHERE INV.operating_unit = OU.organization_id
AND INV.organization_id = hdr.organization_id
AND hdr.boe_id = ln_boe_id;
SELECT org_id
FROM jai_cmn_tax_ctgs_all
WHERE tax_category_id = ln_tax_category_id;
SELECT jitc.tax_category_id,
jctl.tax_id, b.tax_type,
rttv.regime_code
FROM jai_cmn_tax_ctgs_all jitc,
jai_cmn_tax_ctg_lines jctl,
jai_cmn_taxes_all b,
jai_regime_tax_types_v rttv
WHERE jitc.tax_category_id = jctl.tax_category_id
AND jctl.tax_id = b.tax_id
AND rttv.tax_type = b.tax_type
AND jitc.tax_category_id = ln_tax_category_id;
SELECT jitc.tax_category_id,
b.tax_type,
COUNT(jctl.tax_id) cnt_tax_id
FROM jai_cmn_tax_ctgs_all jitc,
jai_cmn_tax_ctg_lines jctl,
jai_cmn_taxes_all b,
jai_regime_tax_types_v rttv
WHERE jitc.tax_category_id = jctl.tax_category_id
AND jctl.tax_id = b.tax_id
AND rttv.tax_type = b.tax_type
AND rttv.regime_code = jai_constants.customs_regime
AND jitc.tax_category_id = ln_tax_category_id
GROUP BY jitc.tax_category_id, b.tax_type, rttv.regime_code;
SELECT set_of_books_id ledger_id
FROM hr_operating_units
WHERE organization_id = v_org_id;
SELECT COUNT(1)
INTO ln_count
FROM gl_period_statuses_v gpsv,
fnd_application fa
WHERE gpsv.application_id = fa.application_id
AND gpsv.closing_status = 'O'
AND pd_date BETWEEN gpsv.start_date AND NVL(gpsv.end_date,pd_date)
AND fa.application_short_name = 'SQLGL'
AND gpsv.set_of_books_id = ln_ledger_id;
SELECT org.boe_account_id boe_account,
hdr.organization_id,
hdr.location_id
FROM JAI_CMN_INVENTORY_ORGS org,
jai_cmn_boe_hdrs_all hdr
WHERE org.organization_id = hdr.organization_id
AND org.location_id = hdr.location_id
AND hdr.boe_id = v_boe_id;
SELECT jbt.tax_type,
SUM(jbt.tax_amount) tax_amt,
SUM(jbt.applied_amount) applied_amt,
(SUM(jbt.tax_amount) - SUM(jbt.applied_amount)) available_amt
FROM jai_boe_details jbd,
jai_boe_detail_taxes jbt
WHERE jbd.boe_id = v_boe_id
AND jbd.boe_detail_id = jbt.boe_detail_id
GROUP BY jbt.tax_type;