CREATE TABLE PORTAL.COMPANY_PROFILE
(
COMPANY_NAME VARCHAR2(100 BYTE),
TOKEN VARCHAR2(100 BYTE),
ADDRESS VARCHAR2(150 BYTE),
NTN_CNIC VARCHAR2(15 BYTE),
TITLE VARCHAR2(150 BYTE)
)
/******************************************/
DROP TABLE SALD CASCADE CONSTRAINTS;
CREATE TABLE SALD
(
GRN_NO VARCHAR2(10 BYTE) NOT NULL,
ITEM_CODE VARCHAR2(10 BYTE),
QTY NUMBER(15),
RATE NUMBER(38,3),
G_VALUE NUMBER(20,2),
ST_RATE NUMBER(20,2),
ST_VALUE NUMBER(20,2),
N_VALUE NUMBER(20,2),
SED NUMBER(20,2),
INV_NO VARCHAR2(10 BYTE),
FREIGHT NUMBER(20,2),
DISC NUMBER(20,2),
TYPE VARCHAR2(1 BYTE),
XFACT NUMBER(20,2),
VRATE NUMBER(20,3),
VATVALUE NUMBER(20,2),
SEDVALUE NUMBER(20,2),
DISC_RATE NUMBER(20,2),
EXT_RATE NUMBER(10,3),
EXT_VALUE NUMBER(15),
FUR_RATE NUMBER(10,3),
FUR_VALUE NUMBER(15),
GROSS_WT NUMBER(15,3),
TARE_WT NUMBER(15,3),
NET_WT NUMBER(15,3),
RETAIL_PRICE_PER_CTN NUMBER(10,2),
RETAIL_PRICE_PER_PC NUMBER(10,2),
RATE_4_ST NUMBER(10,2),
PRICE_AFTER_DISCOUNT NUMBER(10,2),
RATE4ST NUMBER(10,2),
CUSTOM_VALUE_D NUMBER,
GOV_INV_NO VARCHAR2(100 BYTE),
HS_CODE VARCHAR2(100 BYTE),
PROD_NAME VARCHAR2(200 BYTE)
)
TABLESPACE USERS
PCTUSED 0
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 64K
NEXT 1M
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
LOGGING
NOCOMPRESS
NOCACHE;
/************************************************/
DROP TABLE SALM CASCADE CONSTRAINTS;
CREATE TABLE SALM
(
GRN_NO VARCHAR2(10 BYTE),
INV_NO VARCHAR2(10 BYTE),
INV_DATE DATE,
CUS_ID VARCHAR2(10 BYTE),
REM VARCHAR2(50 BYTE),
TRUCK VARCHAR2(40 BYTE),
DOC VARCHAR2(20 BYTE),
GRN_DATE DATE,
TYPE VARCHAR2(1 BYTE),
TERM VARCHAR2(1 BYTE),
TIME VARCHAR2(20 BYTE),
XFACT NUMBER(20,2),
STPAYABLE NUMBER(20,2),
SEDPAYABLE NUMBER(20,2),
FDISCOUNT NUMBER(20,2),
FFREIGHT NUMBER(20,2),
NETVALUE NUMBER(20,2),
TQTY NUMBER(20,2),
GVALUE NUMBER(20,2),
FREIGGHT NUMBER(20,2),
FREIGHT NUMBER(20,2),
EXP VARCHAR2(1 BYTE),
WH_RATE NUMBER(20,3),
WH_AMOUNT NUMBER(20,3),
F_T_RATE NUMBER(10,3),
F_T_AMOUNT NUMBER(15,3),
F_T_U_RATE NUMBER(10,3),
F_T_U_AMOUNT NUMBER(15,3),
EPRCNO VARCHAR2(30 BYTE),
EPRCDATE DATE,
PINO VARCHAR2(30 BYTE),
PIDATE DATE,
EXPNO VARCHAR2(30 BYTE),
FORME VARCHAR2(30 BYTE),
FEDATE DATE,
DOLLAROUT NUMBER(30),
FCRATE NUMBER(30),
VENDOR VARCHAR2(10 BYTE),
BANK VARCHAR2(10 BYTE),
TOFSALES VARCHAR2(30 BYTE),
EXPTERMS VARCHAR2(30 BYTE),
COMINVNO VARCHAR2(30 BYTE),
COMINVDATE DATE,
GDNO VARCHAR2(30 BYTE),
GDDATE DATE,
RBCNO VARCHAR2(30 BYTE),
RBCDATE DATE,
MNTH VARCHAR2(7 BYTE),
PLACEOFSHIPMENT VARCHAR2(200 BYTE),
PLACEOFDESTINATION VARCHAR2(200 BYTE),
PROFORMA_INV_NO VARCHAR2(30 BYTE),
PROFORMA_INV_DATE DATE,
TRANSPORT_DETAILS VARCHAR2(200 BYTE),
SHIPPING_MARK VARCHAR2(200 BYTE),
CUSTOM_POST VARCHAR2(200 BYTE),
INV_CATG NUMBER,
REGIME NUMBER,
COMM_PRCNT NUMBER(10,2),
WHT_COMM_RATE NUMBER(10,2),
WHT_COMM_AMOUNT NUMBER(10,2),
COMM NUMBER(10,2),
ROUTE_NO NUMBER,
GST NUMBER,
INV_TYPE VARCHAR2(10 BYTE),
PASTA_INV_TYPE VARCHAR2(1 BYTE),
COMM_ACCODE VARCHAR2(10 BYTE),
BROKER_CODE VARCHAR2(10 BYTE),
FBRINVNO VARCHAR2(100 BYTE),
SCHEMA_NAME VARCHAR2(100 BYTE),
CUST_NAME VARCHAR2(150 BYTE),
CUST_NTN VARCHAR2(50 BYTE)
)
TABLESPACE USERS
PCTUSED 0
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 64K
NEXT 1M
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
LOGGING
NOCOMPRESS
NOCACHE;
/************************************************/
DECLARE
BEGIN
FOR I IN (SELECT COMPANY_NAME FROM COMPANY_PROFILE)
LOOP
EXECUTE IMMEDIATE 'GRANT DELETE, INSERT, SELECT, UPDATE ON SALM TO "' || I.COMPANY_NAME || '"';
EXECUTE IMMEDIATE 'GRANT DELETE, INSERT, SELECT, UPDATE ON SALD TO "' || I.COMPANY_NAME || '"';
END LOOP;
END;
/************************************************/
CREATE OR REPLACE FUNCTION PORTAL.GET_JSON_PAYLOAD (P_INV VARCHAR2,P_CNAME VARCHAR2) RETURN CLOB IS
l_payload CLOB;
V_NTN VARCHAR2(50);
V_ADDRESS VARCHAR2(100);
V_TITLE VARCHAR2(100);
BEGIN
/* --------------------------------------------------------------------
Build the JSON in one SQL call using JSON_OBJECT and JSON_ARRAYAGG
-------------------------------------------------------------------- */
SELECT NTN_CNIC,ADDRESS,TITLE INTO V_NTN,V_ADDRESS,V_TITLE FROM COMPANY_PROFILE WHERE COMPANY_NAME = P_CNAME;
SELECT JSON_OBJECT (
'invoiceType' VALUE 'Sale Invoice',
'invoiceDate' VALUE TO_CHAR(h.INV_DATE, 'YYYY-MM-DD'),
'sellerNTNCNIC' VALUE V_NTN,
'sellerBusinessName' VALUE V_TITLE,
'sellerProvince' VALUE 'PUNJAB',
'sellerAddress' VALUE V_ADDRESS,
'buyerRegistrationType' VALUE 'Registered',
'buyerNTNCNIC' VALUE H.CUST_NTN,
'buyerBusinessName' VALUE h.CUST_NAME,
'buyerProvince' VALUE 'PUNJAB',
'buyerAddress' VALUE 'KHUSHAB',
'invoiceRefNo' VALUE '',
'scenarioId' VALUE 'SN001',
'items' VALUE (
SELECT JSON_ARRAYAGG (
JSON_OBJECT(
'hsCode' VALUE NVL(i.hs_Code,'5208.1900'),
'productDescription' VALUE i.PROD_NAME,
'rate' VALUE i.ST_RATE||'%',
'uoM' VALUE 'KG',
'quantity' VALUE i.qty,
'totalValues' VALUE round(i.G_VALUE+((i.g_value*i.st_rate)/100),2),
'valueSalesExcludingST' VALUE round(i.G_VALUE,2),
'fixedNotifiedValueOrRetailPrice' VALUE 0,
'salesTaxApplicable' VALUE round((i.g_value*i.st_rate)/100,2),
'salesTaxWithheldAtSource' VALUE 0,
'extraTax' VALUE 0,
'furtherTax' VALUE 0,
'sroScheduleNo' VALUE '',
'fedPayable' VALUE 0,
'discount' VALUE 0,
'saleType' VALUE 'Goods at standard rate (default)',
'sroItemSerialNo' VALUE ''
)
)
FROM sald i
WHERE i.grn_no = h.grn_no
)
RETURNING CLOB )
INTO l_payload
FROM salm h
WHERE h.INV_NO = p_inv;
RETURN l_payload;
end;
/
/************************************************/
BEGIN
DBMS_NETWORK_ACL_ADMIN.create_acl(
acl => 'resolve_acl.xml',
description => 'Allow DNS resolution',
principal => 'PORTAL', -- Your Oracle DB username
is_grant => TRUE,
privilege => 'resolve'
);
END;
/
/*<TOAD_FILE_CHUNK>*/
-- Step 2: Assign ACL to all hosts (or a specific one)
BEGIN
DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL(
acl => 'resolve_acl.xml',
host => '*'
);
END;
/
/************************************************/
In Apex Create Page Item for Token
SQL QUERY (RETURN SINGLE VALUE)
SELECT TOKEN FROM COMPANY_PROFILE WHERE COMPANY_NAME = :MACHINE_NAME;
/************************************************/
Create a Page Item for Getting JSON_PAY_LOAD
P_12_JSON_PAY_LOAD
Create another Page Item P12_INV_NO
Execute Server Side Code ( in Dynamic Action )
BEGIN
SELECT GET_JSON_PAYLOAD(:P12_INV_NO,:MACHINE_NAME) INTO :P12_JSON_PAYLOAD FROM DUAL;
END;
Items to Return P_12_JSON_PAY_LOAD
/************************************************/
Create a Page Item for API Response
P12_RESPONSE
and in Dynamic Action Execute Server Side Code
begin
update salm set FBRINVNO = :P12_RESPONSE where inv_no = :P12_INV_NO;
commit;
end;
/************************************************/
Dynamic Action in P_12_JSON_PAY_LOAD
Execute Java Script Code
const url = 'https://gw.fbr.gov.pk/di_data/v1/di/postinvoicedata_sb';
const token = $v('P12_TOKEN');
console.log(token);
// '722f7406-1ecc-3cf3-9761-d4a00c141c7f';
const json_payload = $v('P12_JSON_PAYLOAD');
console.log(json_payload);
const invoiceData = json_payload;
var popup = apex.widget.waitPopup();
fetch(url, {
method: 'POST',
headers: {
'Authorization': `Bearer ${token}`,
'Content-Type': 'application/json'
},
body: invoiceData //JSON.stringify(invoiceData)
})
.then(response => {
if (response.status === 200) {
console.log('Request successful!');
return response.json();
} else {
console.error(`Request failed with status: ${response.status}`);
popup.remove();
throw new Error(`HTTP error! status: ${response.status}`);
}
})
.then(data => {
console.log('Response received:', data);
// apex.item("P12_RESPONSE").setValue(data.validationResponse.invoiceStatuses[0].invoiceNo);
apex.item("P12_RESPONSE").setValue(data.validationResponse.invoiceStatuses[0].invoiceNo);
popup.remove();
})
.catch(error => {
console.error('Error:', error);
apex.item("P12_RESPONSE").setValue("Error: " + error.message);
popup.remove();
});
/************************ UPDATED CODE To Send JSON **********************/
if (navigator.onLine) {
const url = 'https://gw.fbr.gov.pk/di_data/v1/di/postinvoicedata_sb';
const token = $v('P12_TOKEN');
const json_payload = $v('P12_JSON_PAYLOAD');
const invoiceData = json_payload;
var popup = apex.widget.waitPopup();
fetch(url, {
method: 'POST',
headers: {
'Authorization': `Bearer ${token}`,
'Content-Type': 'application/json'
},
body: invoiceData
})
.then(response => {
if (response.status === 200) {
return response.json();
} else {
console.error(`Request failed with status: ${response.status}`);
popup.remove();
throw new Error(`HTTP error! status: ${response.status}`);
}
})
.then(data => {
if(data.validationResponse.status==="Valid"){
apex.item("P12_RESPONSE").setValue(data.validationResponse.invoiceStatuses[0].invoiceNo);
popup.remove();
} else
{
var detailCount = $v('P12_PCOUNT');
if (detailCount === 0) {
alert(data.validationResponse.error);
} else if (detailCount > 0) {
if(data.validationResponse.error === null || data.validationResponse.error === undefined || data.validationResponse.error === ""){
for (let i=0; i < detailCount; i++ ){
if (data.validationResponse.invoiceStatuses[i].error !== ""){
alert(data.validationResponse.invoiceStatuses[i].error);
}
}
}else{
alert(data.validationResponse.error);
}
}
popup.remove();
}
})
.catch(error => {
alert("Error: " + error.message);
popup.remove();
});
} else {
apex.message.alert("No Internet connection. Please check your connection and try again.");
}
/************************ End of UPDATED CODE To Send JSON **********************/
/************************************************/
Intrective Report Query with Items_To_Submit ( MACHINE_NAME )
Machine_name is Global Variable
select inv_date, a.inv_no, grn_date, a.grn_no, truck, qty, rate, g_value, st_rate, st_value, n_value, gov_inv_no,RATE_4_ST,
'<span id="fbr-post-btn" class="fa fa-check" aria-hidden="true"></span>' as btn
from salm a, sald b
where a.grn_no = b.grn_no
and FBRINVNO is null and a.SCHEMA_NAME = :MACHINE_NAME
Set BTN Column as Link and Paste
javascript:apex.item("P12_INV_NO").setValue("#INV_NO#");
as Target URL
/************************************************/
On Login Form
go to process and Change the Login Process as
apex_authentication.login(
p_username => :P9999_USERNAME,
p_password => :P9999_PASSWORD );
select user_id, deptt_id, section_id,GROUP_ID_CREATED into
:USER_ID, :DEPARTMENT_ID,:SECTION_ID, :GROUP_ID from setup_user
where upper(user_name) = upper(:P9999_USERNAME);
SELECT utl_inaddr.get_host_name(owa_util.get_cgi_env('REMOTE_ADDR')) into :MACHINE_NAME FROM dual;
/************************************************/
