Oracle Apps Technical

Wednesday, 11 March 2020

API to finally close Purchase Requisitions for Approved PR's but not created PO's through PR's :

DECLARE
x_req_control_error_rc VARCHAR2 (500);
l_org_id NUMBER := 28; -- Enter the Operating_Unit Here
cnt NUMBER := 0;
CURSOR c_req_close
IS
SELECT prha.requisition_header_id
,prha.segment1 requisition_num
,prla.line_num
,prla.requisition_line_id
,prha.preparer_id
,prha.type_lookup_code
,pdt.document_type_code
,prha.authorization_status
,prha.closed_code
FROM po_requisition_headers_all prha
,po_requisition_lines_all prla
,po_req_distributions_all prda
,po_document_types_all pdt
WHERE prha.authorization_status = 'APPROVED'
AND prha.requisition_header_id = prla.requisition_header_id
AND prla.requisition_line_id = prda.requisition_line_id
AND prha.type_lookup_code = pdt.document_subtype
AND prha.org_id = pdt.org_id
AND prha.org_id = 28
AND TRUNC(prha.creation_date) >= TRUNC(SYSDATE-160)
AND prha.segment1 = '12161'
AND NOT EXISTS( SELECT 1
FROM po_distributions_all pda
,po_line_locations_all plla
,po_lines_all pla
,po_headers_all pha
WHERE prda.distribution_id = pda.req_distribution_id
AND pda.line_location_id = plla.line_location_id
AND plla.po_line_id = pla.po_line_id
AND pla.po_header_id = pha.po_header_id
)
ORDER BY prha.creation_date, prla.line_num ;
BEGIN
fnd_global.apps_initialize(user_id => 200082
,resp_id => 7673
,resp_appl_id => 201
);
mo_global.init ('PO');
mo_global.set_policy_context ('S', l_org_id);
FOR i IN C_REQ_CLOSE
LOOP
DBMS_OUTPUT.PUT_LINE ('Calling po_reqs_control_sv.update_reqs_status to Finally Close Requisition=>' ||i.requisition_num);
DBMS_OUTPUT.PUT_LINE ('=======================================================');
po_reqs_control_sv.update_reqs_status(x_req_header_id => i.requisition_header_id
,x_req_line_id => i.requisition_line_id
,x_agent_id => i.preparer_id
,x_req_doc_type => i.document_type_code
,x_req_doc_subtype => i.type_lookup_code
,x_req_control_action => 'FINALLY CLOSE'
,x_req_control_reason => 'FINALLY CLOSED BY API'
,x_req_action_date => SYSDATE
,x_encumbrance_flag => 'N'
,x_oe_installed_flag => 'Y'
,x_req_control_error_rc => x_req_control_error_rc
);
DBMS_OUTPUT.PUT_LINE ( 'Status Found: ' ||X_req_control_error_rc);
DBMS_OUTPUT.PUT_LINE ('Requisition Number which is Finally Closed =>'|| i.Requisition_num);
cnt := cnt+1;
END LOOP;
DBMS_OUTPUT.PUT_LINE('Count is :=>' ||cnt);
END;
--==============================================================================
SELECT frt.responsibility_name, frg.request_group_name,
frgu.request_unit_type,frgu.request_unit_id,
fcpt.user_concurrent_program_name
FROM fnd_Responsibility fr, fnd_responsibility_tl frt,
fnd_request_groups frg, fnd_request_group_units frgu,
fnd_concurrent_programs_tl fcpt
WHERE frt.responsibility_id = fr.responsibility_id
AND frg.request_group_id = fr.request_group_id
AND frgu.request_group_id = frg.request_group_id
AND fcpt.concurrent_program_id = frgu.request_unit_id
AND frt.LANGUAGE = USERENV('LANG')
AND fcpt.LANGUAGE = USERENV('LANG')
AND UPPER(fcpt.user_concurrent_program_name) = UPPER(:conc_prg_name)
AND frt.responsibility_name LIKE 'KE%AP%'
ORDER BY 1,2,3,4

select * from fnd_concurrent_programs_tl where UPPER(user_concurrent_program_name) like UPPER('%AP%ACC')

SELECT * FROM po_requisition_headers_all

SELECT prha.requisition_header_id
,prha.segment1
,prla.line_num
,prla.requisition_line_id
,prha.preparer_id
,prha.type_lookup_code
,pdt.document_type_code
,prha.authorization_status
,prha.closed_code
FROM po_requisition_headers_all prha
,po_requisition_lines_all prla
,po_req_distributions_all prda
,po_document_types_all pdt
WHERE prha.authorization_status = 'APPROVED'
AND prha.requisition_header_id = prla.requisition_header_id
AND prla.requisition_line_id = prda.requisition_line_id
AND prha.type_lookup_code = pdt.document_subtype
AND prha.org_id = pdt.org_id
AND prha.org_id = 28
AND TRUNC(prha.creation_date) >= TRUNC(SYSDATE-160)
-- AND prha.segment1 = '12161'
AND NOT EXISTS(SELECT 1
FROM po_distributions_all pda
,po_line_locations_all plla
,po_lines_all pla
,po_headers_all pha
WHERE prda.distribution_id = pda.req_distribution_id
AND pda.line_location_id = plla.line_location_id
AND plla.po_line_id = pla.po_line_id
AND pla.po_header_id = pha.po_header_id
)
ORDER BY prha.creation_date, prla.line_num;

SELECT POH. PO_HEADER_ID, POH. SEGMENT1 "PO NO" , PRHA .SEGMENT1
"REQUISTION NO"
FROM PO_HEADERS_ALL POH,
PO_DISTRIBUTIONS_ALL PDA ,
PO_REQ_DISTRIBUTIONS_ALL PRDA ,
PO_REQUISITION_LINES_ALL PRLA ,
PO_REQUISITION_HEADERS_ALL PRHA
WHERE POH. PO_HEADER_ID = PDA. PO_HEADER_ID
AND PDA. REQ_DISTRIBUTION_ID = PRDA.DISTRIBUTION_ID
AND PRDA. REQUISITION_LINE_ID = PRLA. REQUISITION_LINE_ID
AND PRLA. REQUISITION_HEADER_ID = PRHA. REQUISITION_HEADER_ID
--AND POH.SEGMENT1 = '207655' -- PO NUMBER
--AND PRHA. SEGMENT1 = '230617' -- PO REQUISITION NUMBER
and poh.org_id = 28;

Remove Large Spaces in Report Output :

We can find Long spaces in Invoice Description/Note To Vendor Etc. For avoiding column alignment in report we can remove spaces.

Example:

Solution :

Formula : regexp_replace(i.description,'[[:space:]]+',chr(32));

Monday, 27 January 2020

Advances Plsql Concepts :

1. Execute Immediate:

Example 1:
create or replace procedure xxhrs_local_sql
as
l_select varchar2(200);
begin
--for c1 in 1..10
--loop
l_select :=
'DECLARE '||
'L_NAME VARCHAR2(50); '||
'BEGIN '||
'select ENAME INTO L_NAME'||
' from'||' emp '|| 'where ename '||'like '||'''KING%'''||'; '
||'DBMS_OUTPUT.PUT_LINE(L_NAME); '||
'END;' ;
--DBMS_OUTPUT.PUT_LINE(l_select);
EXECUTE IMMEDIATE l_select;
--end loop;
end;

Example 2:
CREATE OR REPLACE PROCEDURE xxhrrs IS
CURSOR c1 IS
SELECT table_name
FROM user_tables
WHERE table_name in ('DEPT');
--
c1rec c1%ROWTYPE;
--
l_sql VARCHAR2(1000);
BEGIN
FOR r1 IN c1 LOOP
l_sql := 'DECLARE '||
' CURSOR c1 IS '||
' SELECT DEPTNO '||
' FROM '||r1.table_name||'; '||
' c1rec c1%ROWTYPE; '||
'BEGIN '||
' FOR r1 IN c1 LOOP '||
' dbms_output.put_line(''code here...DEPTNO''||r1.deptno); '||
' END LOOP; '||
'END; ';
--
-- DBMS_OUTPUT.PUT_LINE(L_SQL);
EXECUTE IMMEDIATE l_sql;
END LOOP;
END;

Compile the above mentioned Procedures.

Execute Process :
Example 1: exec xxhrrs;
Example 2: exec xxhrs_local_sql;

Developing Excel Report Using Package :

CREATE OR REPLACE PACKAGE APPS.XXhrs_Excel_Out_Proc_AR
AS
PROCEDURE main (
errbuff OUT VARCHAR2,
retcode OUT NUMBER,
P_ORG_ID IN NUMBER,
P_CUSTOMER_TRX_ID IN NUMBER

);

g_legal_entity VARCHAR2(250);
g_organization_id VARCHAR2(250);
g_year NUMBER;
g_start_period_name VARCHAR2(250);
g_end_period_name VARCHAR2(250);
g_vendor_id NUMBER;
g_start_date Date;
g_end_date Date;
g_legal_entity_name VARCHAR2(250);
g_supplier_name VARCHAR2(250);

END XXhrs_Excel_Out_Proc_AR;
/

XXHRS Develop Customer Excel Using Procedure

-------------------------------------------------------------------------------------

CREATE OR REPLACE PACKAGE BODY XXhrs_Excel_Out_Proc_AR
AS
PROCEDURE write_log (p_msg VARCHAR2)
IS
BEGIN
fnd_file.put_line (fnd_file.LOG, p_msg);
EXCEPTION
WHEN OTHERS
THEN
fnd_file.put_line (fnd_file.LOG, 'Exception @write_log ' || SQLERRM);
END;

PROCEDURE write_output (p_msg VARCHAR2)
IS
BEGIN
fnd_file.put_line (fnd_file.output, p_msg);
EXCEPTION
WHEN OTHERS
THEN
write_log ('Exception @ write_output ' || SQLERRM);
END;

PROCEDURE main (
errbuff OUT VARCHAR2,
retcode OUT NUMBER,
P_ORG_ID IN NUMBER,
P_CUSTOMER_TRX_ID IN NUMBER
)
AS
Cursor cur_hdr_data
Is
SELECT HP.PARTY_NAME CUSTOMER_NAME,HP.ADDRESS1 ADDRESS1,HP.ADDRESS2 ADDRESS2,RCTA.CUSTOMER_TRX_ID CUST_TRX_ID,RCTA.TRX_NUMBER INVOICE_NUM
FROM
HZ_CUST_ACCOUNTS HCA,HZ_PARTIES HP,ra_customer_trx_all RCTA
WHERE HCA.PARTY_ID=HP.PARTY_ID AND
RCTA.BILL_TO_CUSTOMER_ID=HCA.CUST_ACCOUNT_ID
AND RCTA.ORG_ID=P_ORG_ID
AND RCTA.CUSTOMER_TRX_ID <COALESCE(P_CUSTOMER_TRX_ID,3000);
l_first_row VARCHAR2(1) := 'N';
l_dist_row VARCHAR2(1) := 'N';
BEGIN
/** POPULATE THE DUMP **/
fnd_file.put_line (fnd_file.output, '<?xml version="1.0" ?>');
fnd_file.put_line (fnd_file.output,
'<?mso-application progid="Excel.Sheet"?>'
);
fnd_file.put_line
(fnd_file.output,
'<Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet" xmlns:o="urn:schemas-microsoft-com:office:office"

xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet" xmlns:html="http://www.w3.org/TR/REC-html40">'
);
fnd_file.put_line
(fnd_file.output,
'<DocumentProperties xmlns="urn:schemas-microsoft-com:office:office">'
);
fnd_file.put_line (fnd_file.output, '</DocumentProperties>');
fnd_file.put_line
(fnd_file.output,
'<ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel">'
);
fnd_file.put_line (fnd_file.output, '<WindowHeight>7935</WindowHeight>');
fnd_file.put_line (fnd_file.output, '<WindowWidth>19035</WindowWidth>');
fnd_file.put_line (fnd_file.output, '<WindowTopX>240</WindowTopX>');
fnd_file.put_line (fnd_file.output, '<WindowTopY>105</WindowTopY>');
fnd_file.put_line (fnd_file.output,
'<ProtectStructure>False</ProtectStructure>'
);
fnd_file.put_line (fnd_file.output,
'<ProtectWindows>False</ProtectWindows>'
);
fnd_file.put_line (fnd_file.output, '</ExcelWorkbook>');
fnd_file.put_line (fnd_file.output, '<Styles>');
fnd_file.put_line (fnd_file.output,
'<Style ss:ID="Default" ss:Name="Normal">'
);
fnd_file.put_line (fnd_file.output, '<Alignment ss:Vertical="Bottom"/>');
fnd_file.put_line (fnd_file.output, '<Borders/>');
fnd_file.put_line
(fnd_file.output,
'<Font ss:FontName="Calibri" x:Family="Swiss" ss:Size="11" ss:Color="#FFFF00"/>'
);
fnd_file.put_line (fnd_file.output, '<Interior/>');
fnd_file.put_line (fnd_file.output, '<NumberFormat/>');
fnd_file.put_line (fnd_file.output, '<Protection/>');
fnd_file.put_line (fnd_file.output, '</Style>');
fnd_file.put_line (fnd_file.output, '<Style ss:ID="m111854504">');
fnd_file.put_line (fnd_file.output,
'<Alignment ss:Vertical="Top" ss:WrapText="1"/>'
);
fnd_file.put_line (fnd_file.output, '<Borders>');
fnd_file.put_line
(fnd_file.output,
'<Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1"
ss:Color="#FFFF00"/>'
);
fnd_file.put_line
(fnd_file.output,
'<Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"
ss:Color="#FFFF00"/>'
);
fnd_file.put_line
(fnd_file.output,
'<Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1"
ss:Color="#FFFF00"/>'
);
fnd_file.put_line
(fnd_file.output,
'<Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"
ss:Color="#FFFF00"/>'
);
fnd_file.put_line (fnd_file.output, '</Borders>');
fnd_file.put_line
(fnd_file.output,
'fnd_file.put_line(fnd_file.output, <Font ss:FontName="Arial" x:Family="Swiss" ss:Color="#00FF00" ss:Bold="1"/>'
);
fnd_file.put_line (fnd_file.output,
'<Interior ss:Color="#0000FF" ss:Pattern="Solid" />'
);
fnd_file.put_line (fnd_file.output, '<NumberFormat/>');
fnd_file.put_line (fnd_file.output, '<Protection/>');
fnd_file.put_line (fnd_file.output, '</Style>');
fnd_file.put_line (fnd_file.output, '<Style ss:ID="s81">');
fnd_file.put_line (fnd_file.output,
'<Alignment ss:Vertical="Top" ss:WrapText="1"/>'
);
------------------------------------------start--------------
fnd_file.put_line (fnd_file.output, '<Borders>');
fnd_file.put_line
(fnd_file.output,
'<Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1"
ss:Color="#000000" />'
);
fnd_file.put_line
(fnd_file.output,
'<Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"
ss:Color="#000000" />'
);
fnd_file.put_line
(fnd_file.output,
'<Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1"
ss:Color="#000000" />'
);
fnd_file.put_line
(fnd_file.output,
'<Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"
ss:Color="#0000FF" />'----------------------------------------used in legal entity above line changed to blue
);
fnd_file.put_line (fnd_file.output, '</Borders>');
----------------------------end------------------
------------------------start-----------------------------
fnd_file.put_line
(fnd_file.output,
'<Font ss:FontName="Calibri" x:Family="Swiss" ss:Color="#FFFFFF" ss:Bold="1" />'
);
fnd_file.put_line (fnd_file.output,
'<Interior ss:Color="#808080" ss:Pattern="Solid"/>' ---it will change the background colour of legal entity grey
);
--- );
fnd_file.put_line (fnd_file.output, '<NumberFormat/>');
fnd_file.put_line (fnd_file.output, '<Protection/>');
fnd_file.put_line (fnd_file.output, '</Style>');
fnd_file.put_line (fnd_file.output, '<Style ss:ID="s82">');
fnd_file.put_line (fnd_file.output,
'<Alignment ss:Vertical="Top" ss:WrapText="1"/>'
);
fnd_file.put_line (fnd_file.output, '<Borders>');
fnd_file.put_line
(fnd_file.output,
'<Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1" ss:Color= "#00FFFF" />'
);
fnd_file.put_line
(fnd_file.output,
'<Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1" ss:Color="#00FFFF" />');
fnd_file.put_line
(fnd_file.output,
'<Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1" ss:Color="#00FFFF" />'
);
fnd_file.put_line
(fnd_file.output,
'<Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1" ss:Color="#00FFFF" />'
);
fnd_file.put_line (fnd_file.output, '</Borders>');
-----------------end --------------------------------
------------------------------------------start----------------------------------
fnd_file.put_line
(fnd_file.output,
'<Font ss:FontName="Calibri" x:Family="Swiss" ss:Color="#000000"/>'
);
fnd_file.put_line (fnd_file.output,
'<Interior ss:Color="#008080" ss:Pattern="Solid"/>'
);
-- fnd_file.put_line (fnd_file.output, '<NumberFormat/>');
fnd_file.put_line (fnd_file.output,
'<NumberFormat ss:Format="#,##0.00;\-#,##0.00" />'
);
fnd_file.put_line (fnd_file.output, '<Protection/>');
fnd_file.put_line (fnd_file.output, '</Style>');
fnd_file.put_line (fnd_file.output, '<Style ss:ID="s83">');
fnd_file.put_line (fnd_file.output,
'<Alignment ss:Vertical="Top" ss:WrapText="1"/>'
);
fnd_file.put_line (fnd_file.output, '<Borders>');
fnd_file.put_line
(fnd_file.output,
'<Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1" ss:Color="#800000"/>' --marrorn (all middle lines effected where data present horizontally
);
fnd_file.put_line
(fnd_file.output,
'<Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1" ss:Color="#800080"/>'---purple(all middle lines effected where data present vertically
);
fnd_file.put_line
(fnd_file.output,
'<Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1" ss:Color="#FFFF00" />'--yellow(right side of invoice below line boared
);
fnd_file.put_line
(fnd_file.output,
'<Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1" ss:Color="#008080"/>'--green
);
fnd_file.put_line (fnd_file.output, '</Borders>');
-----------------------------------end-----------------------------

-----------------------------------start---------------------------
fnd_file.put_line
(fnd_file.output,
'<Font ss:FontName="Calibri" x:Family="Swiss" ss:Color="#FF0000"/>'---red (it will change data appearedfont type and color
);
fnd_file.put_line (fnd_file.output,
'<Interior ss:Color="#C0C0C0" ss:Pattern="Solid"/>'--sliver( it will data appeared background color
);
fnd_file.put_line (fnd_file.output, '<NumberFormat/>');
fnd_file.put_line (fnd_file.output, '<Protection/>');
fnd_file.put_line (fnd_file.output, '</Style>');
fnd_file.put_line (fnd_file.output, '<Style ss:ID="s90">');
fnd_file.put_line
(fnd_file.output,
'<Alignment ss:Horizontal="Left" ss:Vertical="Top" ss:WrapText="1"/>'
);
fnd_file.put_line (fnd_file.output, '<Borders>');
fnd_file.put_line
(fnd_file.output,
'<Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1"
ss:Color="#FF00FF"/>'--Fuchish
);
fnd_file.put_line
(fnd_file.output,
'<Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"
ss:Color="#00FF00" />' --lime
);
fnd_file.put_line
(fnd_file.output,
'<Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1"
ss:Color="#808000" />'--olive parameter information vertical effeted in boarders
);
fnd_file.put_line
(fnd_file.output,
'<Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"
ss:Color="#FF0000"/>'---red (parameter information top line boarders
);
fnd_file.put_line (fnd_file.output, '</Borders>');
--------end-------------------------
------------------start------------------------- dont know
fnd_file.put_line
(fnd_file.output,
'<Font ss:FontName="Calibri" x:Family="Swiss" ss:Color="#00FF00"/>' -- it will change background letter colour to lime
);
fnd_file.put_line (fnd_file.output,
'<Interior ss:Color="#808080" ss:Pattern="Solid"/>' --it will change 204 and 3000 background colour to grey
);
fnd_file.put_line (fnd_file.output, '<NumberFormat/>');
fnd_file.put_line (fnd_file.output, '<Protection/>');
fnd_file.put_line (fnd_file.output, '</Style>');
fnd_file.put_line (fnd_file.output, '<Style ss:ID="s91">');
fnd_file.put_line (fnd_file.output,
'<Alignment ss:Vertical="Top" ss:WrapText="1"/>'
);
fnd_file.put_line (fnd_file.output, '<Borders>');
fnd_file.put_line
(fnd_file.output,
'<Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1"
ss:Color="#00FF00" />' --lime
);
fnd_file.put_line
(fnd_file.output,
'<Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"
ss:Color="#008000" />'--green
);
fnd_file.put_line
(fnd_file.output,
'<Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1"
ss:Color="#808080"/>' --grey
);
fnd_file.put_line
(fnd_file.output,
'<Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"
ss:Color="#FFFFFF"/>'--white
);
fnd_file.put_line (fnd_file.output, '</Borders>');
------------------------------end-------------

------------------------start------------------------- i don't know
fnd_file.put_line
(fnd_file.output,
'<Font ss:FontName="Arial" x:Family="Swiss" ss:Color="#0000FF"/>'--blue
);
fnd_file.put_line (fnd_file.output,
'<Interior ss:Color="#C0C0C0" ss:Pattern="Solid"/>' --grey
);
fnd_file.put_line (fnd_file.output,
'<NumberFormat ss:Format="mmm\-yy"/>'
);
fnd_file.put_line (fnd_file.output, '<Protection/>');
fnd_file.put_line (fnd_file.output, '</Style>');
fnd_file.put_line (fnd_file.output, '<Style ss:ID="s92">');
fnd_file.put_line
(fnd_file.output,
'<Alignment ss:Horizontal="Center" ss:Vertical="Top" ss:WrapText="1"/>'
);

fnd_file.put_line (fnd_file.output, '<Borders>');
fnd_file.put_line
(fnd_file.output,
'<Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1"
ss:Color="#0000FF"/>'
);
fnd_file.put_line
(fnd_file.output,
'<Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"
ss:Color="#0000FF"/>'
);
fnd_file.put_line
(fnd_file.output,
'<Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1"
ss:Color="#0000FF"/>'
);
fnd_file.put_line
(fnd_file.output,
'<Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"
ss:Color="#0000FF"/>'
);
fnd_file.put_line (fnd_file.output, '</Borders>');
------------------------------end------------------------
----------------------------start------------------------------------
fnd_file.put_line
(fnd_file.output,
'<Font ss:FontName="Calibri" x:Family="Swiss" ss:Color="#008080" ss:Bold="1"/>'---teal(it will change title like customer name,address1,address2 text
);
fnd_file.put_line (fnd_file.output,
'<Interior ss:Color="#A7C8E2" ss:Pattern="Solid"/>'---teal(it will change title like customer name,address1,address2 background
);
fnd_file.put_line (fnd_file.output, '<NumberFormat/>');
fnd_file.put_line (fnd_file.output, '<Protection/>');
fnd_file.put_line (fnd_file.output, '</Style>');
fnd_file.put_line (fnd_file.output, '<Style ss:ID="s93">');
fnd_file.put_line
(fnd_file.output,
'<Alignment ss:Horizontal="Left" ss:Vertical="Top" ss:WrapText="1"/>'
);
fnd_file.put_line (fnd_file.output, '<Borders>');
fnd_file.put_line
(fnd_file.output,
'<Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1"
ss:Color="#000000"/>'
);
fnd_file.put_line
(fnd_file.output,
'<Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"
ss:Color="#000000"/>'
);
fnd_file.put_line
(fnd_file.output,
'<Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1"
ss:Color="#000000"/>'
);
fnd_file.put_line
(fnd_file.output,
'<Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"
ss:Color="#000000"/>'
);
fnd_file.put_line (fnd_file.output, '</Borders>');
------------------- end-----------------------------------------
----------------------start--------------------------------------
fnd_file.put_line
(fnd_file.output,
'<Font ss:FontName="Calibri" x:Family="Swiss" ss:Color="#FF0000" ss:Bold="1"/>'---red it will effect in parameter(text) field change text colour
);
fnd_file.put_line (fnd_file.output,
'<Interior ss:Color="#C0C0C0" ss:Pattern="Solid"/>' ------sliver it will effect in parameter(text) field background colour
);
fnd_file.put_line (fnd_file.output, '<NumberFormat/>');
fnd_file.put_line (fnd_file.output, '<Protection/>');
fnd_file.put_line (fnd_file.output, '</Style>');
fnd_file.put_line (fnd_file.output, '<Style ss:ID="s94">');
fnd_file.put_line
(fnd_file.output,
'<Alignment ss:Horizontal="Center" ss:Vertical="Bottom"/>'
);
fnd_file.put_line (fnd_file.output, '<Borders>');
fnd_file.put_line
(fnd_file.output,
'<Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="2" ss:Color="#000000"/>'
);
fnd_file.put_line
(fnd_file.output,
'<Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="2" ss:Color="#000000"/>'
);
fnd_file.put_line
(fnd_file.output,
'<Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="2" ss:Color="#000000"/>'
);
fnd_file.put_line
(fnd_file.output,
'<Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="2" ss:Color="#000000"/>'
);
fnd_file.put_line (fnd_file.output, '</Borders>');
---------------------------end-------------------------------------
--------start---------
fnd_file.put_line
(fnd_file.output,
'<Font ss:FontName="Calibri" x:Family="Swiss" ss:Size="13" ss:Color="#000000" ss:Bold="1"/>' ---balck it will effect Customer Transaction Report text
);
fnd_file.put_line (fnd_file.output,
'<Interior ss:Color="#2172B3" ss:Pattern="Solid"/>'---balck it will effect Customer Transaction Report background clr
);
fnd_file.put_line (fnd_file.output, '<NumberFormat/>');
fnd_file.put_line (fnd_file.output, '<Protection/>');
fnd_file.put_line (fnd_file.output, '</Style>');
fnd_file.put_line (fnd_file.output, '<Style ss:ID="s96">');
fnd_file.put_line
(fnd_file.output,
'<Alignment ss:Horizontal="Center" ss:Vertical="Bottom"/>'
);
fnd_file.put_line (fnd_file.output, '<Borders>');
fnd_file.put_line
(fnd_file.output,
'<Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="2" ss:Color="#000000"/>'
);
fnd_file.put_line
(fnd_file.output,
'<Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="2" ss:Color="#000000"/>'
);
fnd_file.put_line
(fnd_file.output,
'<Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="2" ss:Color="#000000"/>'
);
fnd_file.put_line
(fnd_file.output,
'<Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="2" ss:Color="#000000"/>'
);
fnd_file.put_line (fnd_file.output, '</Borders>');
fnd_file.put_line
(fnd_file.output,
'<Font ss:FontName="Calibri" x:Family="Swiss" ss:Size="13" ss:Color="#000000" ss:Bold="1"/>'
);
fnd_file.put_line (fnd_file.output,
'<Interior ss:Color="#A7C8E2" ss:Pattern="Solid"/>'
);
fnd_file.put_line (fnd_file.output, '<NumberFormat/>');
fnd_file.put_line (fnd_file.output, '<Protection/>');
fnd_file.put_line (fnd_file.output, '</Style>');
fnd_file.put_line (fnd_file.output, '<Style ss:ID="s114">');
fnd_file.put_line (fnd_file.output,
'<Alignment ss:Vertical="Bottom" ss:WrapText="1"/>'
);
fnd_file.put_line (fnd_file.output, '<Borders>');
fnd_file.put_line
(fnd_file.output,
'<Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"
ss:Color="#000000"/>'
);
fnd_file.put_line (fnd_file.output, '</Borders>');
-----------------end------------------------------------
----------------start------------- i dont know
fnd_file.put_line
(fnd_file.output,
'<Font ss:FontName="Calibri" x:Family="Swiss" ss:Size="11" ss:Color="#FF00FF"/>'--purple
);
fnd_file.put_line (fnd_file.output,
'<Interior ss:Color="#000000" ss:Pattern="Solid"/>'--black
);
fnd_file.put_line (fnd_file.output, '<NumberFormat/>');
fnd_file.put_line (fnd_file.output, '<Protection/>');
fnd_file.put_line (fnd_file.output, '</Style>');
fnd_file.put_line (fnd_file.output, '<Style ss:ID="s128">');
fnd_file.put_line
(fnd_file.output,
'<Alignment ss:Horizontal="Center" ss:Vertical="Bottom" ss:WrapText="1"/>'
);
fnd_file.put_line (fnd_file.output, '<Borders>');
fnd_file.put_line
(fnd_file.output,
'<Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"
ss:Color="#000000"/>'
);
fnd_file.put_line (fnd_file.output, '</Borders>');
-------------------end------------------
------------------start---------------- i dont know
fnd_file.put_line
(fnd_file.output,
' <Font ss:FontName="Arial" x:Family="Swiss" ss:Size="15" ss:Color="#008080"
ss:Bold="1"/>'
);
fnd_file.put_line (fnd_file.output,
'<Interior ss:Color="#000000" ss:Pattern="Solid"/>'
);
fnd_file.put_line (fnd_file.output, '<NumberFormat/>');
fnd_file.put_line (fnd_file.output, '<Protection/>');
fnd_file.put_line (fnd_file.output, '</Style>');
fnd_file.put_line (fnd_file.output, '<Style ss:ID="s130">');
fnd_file.put_line (fnd_file.output,
'<Alignment ss:Vertical="Bottom" ss:WrapText="1"/>'
);
fnd_file.put_line (fnd_file.output, '<Borders>');
fnd_file.put_line
(fnd_file.output,
'<Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1"
ss:Color="#000000"/>'
);
fnd_file.put_line
(fnd_file.output,
'<Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"
ss:Color="#000000"/>'
);
fnd_file.put_line (fnd_file.output, '</Borders>');
-------------end--------------
-------------------start----- i dont know
fnd_file.put_line
(fnd_file.output,
'<Font ss:FontName="Calibri" x:Family="Swiss" ss:Size="11" ss:Color="#FF0000"/>'
);
fnd_file.put_line (fnd_file.output,
'<Interior ss:Color="#FFFFFF" ss:Pattern="Solid"/>'
);
fnd_file.put_line (fnd_file.output, '<NumberFormat/>');
fnd_file.put_line (fnd_file.output, '<Protection/>');
fnd_file.put_line (fnd_file.output, '</Style>');
fnd_file.put_line (fnd_file.output, '</Styles>');
fnd_file.put_line (fnd_file.output,
'<Worksheet ss:Name="Supplier Payments">'
);
fnd_file.put_line
(fnd_file.output,
'<Table x:FullColumns="1" x:FullRows="1" ss:DefaultRowHeight="15">'
);
fnd_file.put_line (fnd_file.output,
'<Column ss:AutoFitWidth="0" ss:Width="127.5"/>'
);
fnd_file.put_line
(fnd_file.output,
'<Column ss:AutoFitWidth="0" ss:Width="66.75" ss:Span="11"/>'
);
fnd_file.put_line (fnd_file.output, '<Row ss:Height="19.5">'); --row start
-- ln_mergeacross := g_period_number + 4; ------display title -------
fnd_file.put_line
(fnd_file.output,
'<Cell ss:MergeAcross="15"'
|| ' ss:StyleID="s94"><Data ss:Type="String"> Customer Transaction Report </Data></Cell>'
); -----end of title---
fnd_file.put_line (fnd_file.output, '</Row>'); --row end
-----display parameter title -------
fnd_file.put_line (fnd_file.output, '<Row ss:Index="3">'); --row start
fnd_file.put_line
(fnd_file.output,
'<Cell ss:StyleID="s93"><Data ss:Type="String">Parameters</Data></Cell>'
);
fnd_file.put_line (fnd_file.output, '</Row>'); --row end
-------end of parameter title----
--------------------------------------------------------------------------------------------------------
fnd_file.put_line (fnd_file.output, '<Row ss:Index="4">'); --row start --Cell[ss:Index="1"] '<Cell ss:Index="3">''
fnd_file.put_line
(fnd_file.output,
'<Cell ss:StyleID="s81"><Data ss:Type="String">Parameters23</Data></Cell>'
);
fnd_file.put_line (fnd_file.output, '</Row>'); --row end
------------------------------------------------------------------------------------------------
fnd_file.put_line (fnd_file.output, '<Row>'); --row start
fnd_file.put_line (fnd_file.output, '</Row>'); --row end

/****************** Parameters *************/

fnd_file.put_line (fnd_file.output, '<Row ss:AutoFitHeight="0">');
fnd_file.put_line
(fnd_file.output,
'<Cell ss:StyleID="s81"><Data ss:Type="String">Legal Entity </Data></Cell>'
);
fnd_file.put_line (fnd_file.output,
'<Cell ss:StyleID="s90"><Data ss:Type="String">'
|| p_org_id
|| '</Data></Cell>'
);
-- fnd_file.put_line (fnd_file.output, '</Row>');
fnd_file.put_line (fnd_file.output,
'<Cell ss:StyleID="s90"><Data ss:Type="String">'
|| P_CUSTOMER_TRX_ID
|| '</Data></Cell>'
);
fnd_file.put_line (fnd_file.output, '</Row>');

/****************** end of Parameters *************/

/**************** Result Table Starts from here ****************/

fnd_file.put_line (fnd_file.output,
'<Row ss:AutoFitHeight="0" ss:Height="15">'
);

fnd_file.put_line
(fnd_file.output,
'<Cell ss:StyleID="s92"><Data ss:Type="String">CUSTOMER NAME</Data></Cell>'
);
fnd_file.put_line
(fnd_file.output,
'<Cell ss:StyleID="s92"><Data ss:Type="String">ADDRESS1</Data></Cell>'
);
fnd_file.put_line
(fnd_file.output,
'<Cell ss:StyleID="s92"><Data ss:Type="String"> ADDRESS2</Data></Cell>'
);
fnd_file.put_line
(fnd_file.output,
'<Cell ss:StyleID="s92"><Data ss:Type="String"> CUST TRX ID</Data></Cell>'
);
fnd_file.put_line
(fnd_file.output,
'<Cell ss:StyleID="s92"><Data ss:Type="String">INVOICE NUMBER</Data></Cell>'
);
fnd_file.put_line (fnd_file.output, '</Row>');
FOR rec IN cur_hdr_data
LOOP
fnd_file.put_line (fnd_file.output,
'<Row ss:AutoFitHeight="0" ss:Height="15">'
);

fnd_file.put_line
(fnd_file.output,
'<Cell ss:StyleID="s83"><Data ss:Type="String">'
|| rec.CUSTOMER_NAME
|| '</Data></Cell>'
);

fnd_file.put_line
(fnd_file.output,
'<Cell ss:StyleID="s83"><Data ss:Type="String">'
|| rec.ADDRESS1
|| '</Data></Cell>'
);

fnd_file.put_line
(fnd_file.output,
'<Cell ss:StyleID="s83"><Data ss:Type="String">'
|| rec.ADDRESS2
|| '</Data></Cell>'
);
fnd_file.put_line
(fnd_file.output,
'<Cell ss:StyleID="s83"><Data ss:Type="String">'
|| rec.CUST_TRX_ID
|| '</Data></Cell>'
);
fnd_file.put_line
(fnd_file.output,
'<Cell ss:StyleID="s83"><Data ss:Type="String">'
|| rec.INVOICE_NUM
|| '</Data></Cell>'
);
fnd_file.put_line (fnd_file.output, '</Row>');
End Loop;
fnd_file.put_line (fnd_file.output, '</Table>');
fnd_file.put_line (fnd_file.output, '</Worksheet>');
fnd_file.put_line (fnd_file.output, '</Workbook>');
END;

END XXhrs_Excel_Out_Proc_AR;

Monday, 26 March 2018

Reverse Year Wise Data

input a date and an year number

I want to input a date and an year number in integer want the below output.

--10 is year as input
--30-Sep-2017 is input date (I need 30-sep-2017,30-Sep-2016,30-Sep-2015 till the number of year i.e. 10 in this case )

CREATE OR REPLACE PROCEDURE XXHRS_FB_DATES(P_START_DATE DATE,P_NUMBER_YEARS NUMBER)
AS
L_START_DATE DATE:=P_START_DATE;
L_RETRIVE_DATA DATE;
BEGIN
SELECT ADD_MONTHS(L_START_DATE,12) INTO L_RETRIVE_DATA FROM DUAL;
FOR LION IN 1..P_NUMBER_YEARS LOOP
DBMS_OUTPUT.PUT_LINE(L_RETRIVE_DATA);
L_RETRIVE_DATA :=ADD_MONTHS(L_RETRIVE_DATA,-12);
END LOOP;
END;

EXEC XXHRS_FB_DATES('15-JAN-2018',5)

Function Queries(Return More Than one Rows)

Function Queries(Return More Than one Rows)

1.In table ABC i have columns like
name gender sal
------ ------- -----
a M 1000
b F
c F 2000
d M
e F 3000
I want to change gender from 'M' to 'F' and 'F' to 'M
where ever salary is null.How its possible??

create or replace type xxorc_fb as object
(gender varchar2(20));

create or replace type xxorc_fb_ta as table of xxorc_fb;

CREATE OR REPLACE function XXHRS_FB3
RETURN xxorc_fb_ta
as
p_gen1 xxorc_fb_ta;
p_gen xxorc_fb_ta;
begin
--select xxorc_fb(GENDER)bulk collect into p_gen FROM XXHRS_FB2 WHERE id is null;
SELECT xxorc_fb( CASE GENDER WHEN 'M' THEN 'F'
WHEN 'F' THEN 'M'
ELSE 'G' END )bulk collect into p_gen1
FROM XXHRS_FB2 WHERE id is null;
return p_gen1;
end;

select XXHRS_FB3 from dual

Any Queries Mail Me vivekexpertize@gmail.com

Sunday, 25 March 2018

Customer Transaction Interface

AR Customer Transaction Interface:

INSERT INTO ra_interface_lines_all
(INTERFACE_LINE_ID " must if not error occured in main program",interface_line_context,
interface_line_attribute1,
batch_source_name "RA_BATCH_SOURCES_ALL"
, set_of_books_id,
description, taxable_flag,
amount_includes_tax_flag, line_type,
currency_code, amount,
cust_trx_type_name, term_name,
orig_system_bill_customer_id "pARTY_ID",
orig_system_bill_address_id "HZ_CUST_ACCT_SITES_ALL.ORIG_SYSTEM_REFERENCE.user_org_id.user_present_using_responsibility",
orig_system_bill_customer_ref,
orig_system_bill_address_ref,
conversion_type, conversion_rate,
trx_date, gl_date "MUST_BE_OPEN", trx_number,
quantity, unit_selling_price,
created_by, creation_date,
last_updated_by, last_update_date,
last_update_login, org_id,CUST_TRX_TYPE_ID) VALUES
(54454544,'CONVERSION',RA_CUST_TRX_LINE_SALESREPS_S.NEXTVAL,'LEGACY',
1,'Ar_Defense_Cust1',NULL,'N','LINE','USD',100,'Invoice','IMMEDIATE',
1146,4041,NULL,NULL,'User',1,SYSDATE,'25-MAR-2017',NULL,3,
200,FND_GLOBAL.LOGIN_ID,SYSDATE,FND_GLOBAL.LOGIN_ID,SYSDATE,FND_GLOBAL.LOGIN_ID,204,1)

---------------------------------------------------------------------------------------------------------------------------

SELECT RECEIPT_METHOD_ID FROM RA_interface_lines_all where INTERFACE_LINE_ID=1296256



SELECT * FROM RA_interface_lines_all where INTERFACE_LINE_ID=54454544

WHERE --INTERFACE_LINE_ATTRIBUTE1='700538'
batch_source_name='LEGACY' ORDER BY CREATION_DATE DESC

SELECT FND_GLOBAL.LOGIN_ID FROM DUAL

SELECT FND_PROFILE.VALUE('USER_ID') FROM DUAL

SELECT * FROM RA_INTERFACE_ERRORS

SELECT * FROM DBA_SEQUENCES WHERE SEQUENCE_NAME LIKE 'RA_%'

SELECT * FROM RA_BATCH_SOURCES_ALL WHERE NAME LIKE 'CONVERSION'

SELECT * FROM GL_LEDGERS WHERE NAME LIKE 'Visi%Op%'

SELECT * FROM HZ_PARTIES WHERE PARTY_NAME LIKE 'Department of Defense%'

SELECT * FROM HZ_CUST_ACCT_SITES_ALL WHERE CUST_ACCOUNT_ID=1146 and org_id=204

SELECT * FROM HZ_CUST_ACCOUNTS WHERE CUST_ACCOUNT_ID=1146

SELECT * FROM RA_CUST_TRX_TYPES_ALL-- where CUST_TRX_TYPE_ID=1923

SELECT * FROM HZ_LOCATIONS --WHERE LOCATION_ID=4403

SELECT * FROM RA_CUSTOMER_TRX_ALL WHERE BILL_TO_CUSTOMER_ID=1146 AND TRX_NUMBER='11064'

SELECT * FROM RA_TERMS WHERE NAME LIKE '2/10, Net 30%'

SELECT CUST_TRX_TYPE_ID FROM RA_INTERFACE_LINES_ALL

SELECT * FROM AR_RECEIPT_METHODS WHERE NAME LIKE 'Automatic Receipts%' -- 1363

--------------------------------------------------------------------------------------------------------

Single Insertion Script :

SET DEFINE OFF;
Insert into AR.RA_INTERFACE_LINES_ALL
(INTERFACE_LINE_ID, INTERFACE_LINE_CONTEXT, INTERFACE_LINE_ATTRIBUTE1, INTERFACE_LINE_ATTRIBUTE2, INTERFACE_LINE_ATTRIBUTE3, INTERFACE_LINE_ATTRIBUTE4, INTERFACE_LINE_ATTRIBUTE5, INTERFACE_LINE_ATTRIBUTE6, INTERFACE_LINE_ATTRIBUTE7, INTERFACE_LINE_ATTRIBUTE8, BATCH_SOURCE_NAME, SET_OF_BOOKS_ID, LINE_TYPE, DESCRIPTION, CURRENCY_CODE, AMOUNT, CUST_TRX_TYPE_NAME, CUST_TRX_TYPE_ID, TERM_NAME, TERM_ID, ORIG_SYSTEM_BATCH_NAME, ORIG_SYSTEM_BILL_CUSTOMER_REF, ORIG_SYSTEM_BILL_CUSTOMER_ID, ORIG_SYSTEM_BILL_ADDRESS_REF, ORIG_SYSTEM_BILL_ADDRESS_ID, ORIG_SYSTEM_BILL_CONTACT_REF, ORIG_SYSTEM_BILL_CONTACT_ID, ORIG_SYSTEM_SHIP_CUSTOMER_REF, ORIG_SYSTEM_SHIP_CUSTOMER_ID, ORIG_SYSTEM_SHIP_ADDRESS_REF, ORIG_SYSTEM_SHIP_ADDRESS_ID, ORIG_SYSTEM_SHIP_CONTACT_REF, ORIG_SYSTEM_SHIP_CONTACT_ID, ORIG_SYSTEM_SOLD_CUSTOMER_REF, ORIG_SYSTEM_SOLD_CUSTOMER_ID, LINK_TO_LINE_ID, LINK_TO_LINE_CONTEXT, LINK_TO_LINE_ATTRIBUTE1, LINK_TO_LINE_ATTRIBUTE2, LINK_TO_LINE_ATTRIBUTE3, LINK_TO_LINE_ATTRIBUTE4, LINK_TO_LINE_ATTRIBUTE5, LINK_TO_LINE_ATTRIBUTE6, LINK_TO_LINE_ATTRIBUTE7, RECEIPT_METHOD_NAME, RECEIPT_METHOD_ID, CONVERSION_TYPE, CONVERSION_DATE, CONVERSION_RATE, CUSTOMER_TRX_ID, TRX_DATE, GL_DATE, DOCUMENT_NUMBER, TRX_NUMBER, LINE_NUMBER, QUANTITY, QUANTITY_ORDERED, UNIT_SELLING_PRICE, UNIT_STANDARD_PRICE, PRINTING_OPTION, INTERFACE_STATUS, REQUEST_ID, RELATED_BATCH_SOURCE_NAME, RELATED_TRX_NUMBER, RELATED_CUSTOMER_TRX_ID, PREVIOUS_CUSTOMER_TRX_ID, CREDIT_METHOD_FOR_ACCT_RULE, CREDIT_METHOD_FOR_INSTALLMENTS, REASON_CODE, TAX_RATE, TAX_CODE, TAX_PRECEDENCE, EXCEPTION_ID, EXEMPTION_ID, SHIP_DATE_ACTUAL, FOB_POINT, SHIP_VIA, WAYBILL_NUMBER, INVOICING_RULE_NAME, INVOICING_RULE_ID, ACCOUNTING_RULE_NAME, ACCOUNTING_RULE_ID, ACCOUNTING_RULE_DURATION, RULE_START_DATE, PRIMARY_SALESREP_NUMBER, PRIMARY_SALESREP_ID, SALES_ORDER, SALES_ORDER_LINE, SALES_ORDER_DATE, SALES_ORDER_SOURCE, SALES_ORDER_REVISION, PURCHASE_ORDER, PURCHASE_ORDER_REVISION, PURCHASE_ORDER_DATE, AGREEMENT_NAME, AGREEMENT_ID, MEMO_LINE_NAME, MEMO_LINE_ID, INVENTORY_ITEM_ID, MTL_SYSTEM_ITEMS_SEG1, MTL_SYSTEM_ITEMS_SEG2, MTL_SYSTEM_ITEMS_SEG3, MTL_SYSTEM_ITEMS_SEG4, MTL_SYSTEM_ITEMS_SEG5, MTL_SYSTEM_ITEMS_SEG6, MTL_SYSTEM_ITEMS_SEG7, MTL_SYSTEM_ITEMS_SEG8, MTL_SYSTEM_ITEMS_SEG9, MTL_SYSTEM_ITEMS_SEG10, MTL_SYSTEM_ITEMS_SEG11, MTL_SYSTEM_ITEMS_SEG12, MTL_SYSTEM_ITEMS_SEG13, MTL_SYSTEM_ITEMS_SEG14, MTL_SYSTEM_ITEMS_SEG15, MTL_SYSTEM_ITEMS_SEG16, MTL_SYSTEM_ITEMS_SEG17, MTL_SYSTEM_ITEMS_SEG18, MTL_SYSTEM_ITEMS_SEG19, MTL_SYSTEM_ITEMS_SEG20, REFERENCE_LINE_ID, REFERENCE_LINE_CONTEXT, REFERENCE_LINE_ATTRIBUTE1, REFERENCE_LINE_ATTRIBUTE2, REFERENCE_LINE_ATTRIBUTE3, REFERENCE_LINE_ATTRIBUTE4, REFERENCE_LINE_ATTRIBUTE5, REFERENCE_LINE_ATTRIBUTE6, REFERENCE_LINE_ATTRIBUTE7, TERRITORY_ID, TERRITORY_SEGMENT1, TERRITORY_SEGMENT2, TERRITORY_SEGMENT3, TERRITORY_SEGMENT4, TERRITORY_SEGMENT5, TERRITORY_SEGMENT6, TERRITORY_SEGMENT7, TERRITORY_SEGMENT8, TERRITORY_SEGMENT9, TERRITORY_SEGMENT10, TERRITORY_SEGMENT11, TERRITORY_SEGMENT12, TERRITORY_SEGMENT13, TERRITORY_SEGMENT14, TERRITORY_SEGMENT15, TERRITORY_SEGMENT16, TERRITORY_SEGMENT17, TERRITORY_SEGMENT18, TERRITORY_SEGMENT19, TERRITORY_SEGMENT20, ATTRIBUTE_CATEGORY, ATTRIBUTE1, ATTRIBUTE2, ATTRIBUTE3, ATTRIBUTE4, ATTRIBUTE5, ATTRIBUTE6, ATTRIBUTE7, ATTRIBUTE8, ATTRIBUTE9, ATTRIBUTE10, ATTRIBUTE11, ATTRIBUTE12, ATTRIBUTE13, ATTRIBUTE14, ATTRIBUTE15, HEADER_ATTRIBUTE_CATEGORY, HEADER_ATTRIBUTE1, HEADER_ATTRIBUTE2, HEADER_ATTRIBUTE3, HEADER_ATTRIBUTE4, HEADER_ATTRIBUTE5, HEADER_ATTRIBUTE6, HEADER_ATTRIBUTE7, HEADER_ATTRIBUTE8, HEADER_ATTRIBUTE9, HEADER_ATTRIBUTE10, HEADER_ATTRIBUTE11, HEADER_ATTRIBUTE12, HEADER_ATTRIBUTE13, HEADER_ATTRIBUTE14, HEADER_ATTRIBUTE15, COMMENTS, INTERNAL_NOTES, INITIAL_CUSTOMER_TRX_ID, USSGL_TRANSACTION_CODE_CONTEXT, USSGL_TRANSACTION_CODE, ACCTD_AMOUNT, CUSTOMER_BANK_ACCOUNT_ID, CUSTOMER_BANK_ACCOUNT_NAME, UOM_CODE, UOM_NAME, DOCUMENT_NUMBER_SEQUENCE_ID, LINK_TO_LINE_ATTRIBUTE10, LINK_TO_LINE_ATTRIBUTE11, LINK_TO_LINE_ATTRIBUTE12, LINK_TO_LINE_ATTRIBUTE13, LINK_TO_LINE_ATTRIBUTE14, LINK_TO_LINE_ATTRIBUTE15, LINK_TO_LINE_ATTRIBUTE8, LINK_TO_LINE_ATTRIBUTE9, REFERENCE_LINE_ATTRIBUTE10, REFERENCE_LINE_ATTRIBUTE11, REFERENCE_LINE_ATTRIBUTE12, REFERENCE_LINE_ATTRIBUTE13, REFERENCE_LINE_ATTRIBUTE14, REFERENCE_LINE_ATTRIBUTE15, REFERENCE_LINE_ATTRIBUTE8, REFERENCE_LINE_ATTRIBUTE9, INTERFACE_LINE_ATTRIBUTE10, INTERFACE_LINE_ATTRIBUTE11, INTERFACE_LINE_ATTRIBUTE12, INTERFACE_LINE_ATTRIBUTE13, INTERFACE_LINE_ATTRIBUTE14, INTERFACE_LINE_ATTRIBUTE15, INTERFACE_LINE_ATTRIBUTE9, VAT_TAX_ID, REASON_CODE_MEANING, LAST_PERIOD_TO_CREDIT, PAYING_CUSTOMER_ID, PAYING_SITE_USE_ID, TAX_EXEMPT_FLAG, TAX_EXEMPT_REASON_CODE, TAX_EXEMPT_REASON_CODE_MEANING, TAX_EXEMPT_NUMBER, SALES_TAX_ID, CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN, LOCATION_SEGMENT_ID, MOVEMENT_ID, ORG_ID, AMOUNT_INCLUDES_TAX_FLAG, HEADER_GDF_ATTR_CATEGORY, HEADER_GDF_ATTRIBUTE1, HEADER_GDF_ATTRIBUTE2, HEADER_GDF_ATTRIBUTE3, HEADER_GDF_ATTRIBUTE4, HEADER_GDF_ATTRIBUTE5, HEADER_GDF_ATTRIBUTE6, HEADER_GDF_ATTRIBUTE7, HEADER_GDF_ATTRIBUTE8, HEADER_GDF_ATTRIBUTE9, HEADER_GDF_ATTRIBUTE10, HEADER_GDF_ATTRIBUTE11, HEADER_GDF_ATTRIBUTE12, HEADER_GDF_ATTRIBUTE13, HEADER_GDF_ATTRIBUTE14, HEADER_GDF_ATTRIBUTE15, HEADER_GDF_ATTRIBUTE16, HEADER_GDF_ATTRIBUTE17, HEADER_GDF_ATTRIBUTE18, HEADER_GDF_ATTRIBUTE19, HEADER_GDF_ATTRIBUTE20, HEADER_GDF_ATTRIBUTE21, HEADER_GDF_ATTRIBUTE22, HEADER_GDF_ATTRIBUTE23, HEADER_GDF_ATTRIBUTE24, HEADER_GDF_ATTRIBUTE25, HEADER_GDF_ATTRIBUTE26, HEADER_GDF_ATTRIBUTE27, HEADER_GDF_ATTRIBUTE28, HEADER_GDF_ATTRIBUTE29, HEADER_GDF_ATTRIBUTE30, LINE_GDF_ATTR_CATEGORY, LINE_GDF_ATTRIBUTE1, LINE_GDF_ATTRIBUTE2, LINE_GDF_ATTRIBUTE3, LINE_GDF_ATTRIBUTE4, LINE_GDF_ATTRIBUTE5, LINE_GDF_ATTRIBUTE6, LINE_GDF_ATTRIBUTE7, LINE_GDF_ATTRIBUTE8, LINE_GDF_ATTRIBUTE9, LINE_GDF_ATTRIBUTE10, LINE_GDF_ATTRIBUTE11, LINE_GDF_ATTRIBUTE12, LINE_GDF_ATTRIBUTE13, LINE_GDF_ATTRIBUTE14, LINE_GDF_ATTRIBUTE15, LINE_GDF_ATTRIBUTE16, LINE_GDF_ATTRIBUTE17, LINE_GDF_ATTRIBUTE18, LINE_GDF_ATTRIBUTE19, LINE_GDF_ATTRIBUTE20, RESET_TRX_DATE_FLAG, PAYMENT_SERVER_ORDER_NUM, APPROVAL_CODE, ADDRESS_VERIFICATION_CODE, WAREHOUSE_ID, TRANSLATED_DESCRIPTION, CONS_BILLING_NUMBER, PROMISED_COMMITMENT_AMOUNT, PAYMENT_SET_ID, ORIGINAL_GL_DATE, CONTRACT_LINE_ID, CONTRACT_ID, SOURCE_DATA_KEY1, SOURCE_DATA_KEY2, SOURCE_DATA_KEY3, SOURCE_DATA_KEY4, SOURCE_DATA_KEY5, INVOICED_LINE_ACCTG_LEVEL, OVERRIDE_AUTO_ACCOUNTING_FLAG, SOURCE_APPLICATION_ID, SOURCE_EVENT_CLASS_CODE, SOURCE_ENTITY_CODE, SOURCE_TRX_ID, SOURCE_TRX_LINE_ID, SOURCE_TRX_LINE_TYPE, SOURCE_TRX_DETAIL_TAX_LINE_ID, HISTORICAL_FLAG, TAX_REGIME_CODE, TAX, TAX_STATUS_CODE, TAX_RATE_CODE, TAX_JURISDICTION_CODE, TAXABLE_AMOUNT, TAXABLE_FLAG, LEGAL_ENTITY_ID, PARENT_LINE_ID, DEFERRAL_EXCLUSION_FLAG, PAYMENT_TRXN_EXTENSION_ID, RULE_END_DATE, PAYMENT_ATTRIBUTES, APPLICATION_ID, BILLING_DATE, TRX_BUSINESS_CATEGORY, PRODUCT_FISC_CLASSIFICATION, PRODUCT_CATEGORY, PRODUCT_TYPE, LINE_INTENDED_USE, ASSESSABLE_VALUE, DOCUMENT_SUB_TYPE, DEFAULT_TAXATION_COUNTRY, USER_DEFINED_FISC_CLASS, TAXED_UPSTREAM_FLAG, TAX_INVOICE_DATE, TAX_INVOICE_NUMBER, PAYMENT_TYPE_CODE, MANDATE_LAST_TRX_FLAG)
Values
(54454544, 'CONVERSION', '700542', NULL, NULL,
NULL, NULL, NULL, NULL, NULL,
'LEGACY', 1, 'LINE', 'Ar_Defense_Cust1', 'USD',
100, 'Invoice', 1, 'IMMEDIATE', 5,
NULL, NULL, 1146, NULL, 4041,
NULL, NULL, NULL, NULL, NULL,
NULL, NULL, NULL, NULL, NULL,
NULL, NULL, NULL, NULL, NULL,
NULL, NULL, NULL, NULL, NULL,
NULL, 'User', NULL, 1, NULL,
TO_DATE('03/21/2018 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('12/20/2017 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), NULL, NULL, NULL,
3, NULL, 200, NULL, NULL,
NULL, NULL, NULL, NULL, NULL,
NULL, NULL, NULL, NULL, NULL,
NULL, NULL, NULL, NULL, NULL,
NULL, NULL, NULL, NULL, NULL,
NULL, NULL, NULL, NULL, NULL,
NULL, NULL, NULL, NULL, NULL,
NULL, NULL, NULL, NULL, NULL,
NULL, NULL, NULL, NULL, NULL,
NULL, NULL, NULL, NULL, NULL,
NULL, NULL, NULL, NULL, NULL,
NULL, NULL, NULL, NULL, NULL,
NULL, NULL, NULL, NULL, NULL,
NULL, NULL, NULL, NULL, NULL,
NULL, NULL, NULL, NULL, NULL,
NULL, NULL, NULL, NULL, NULL,
NULL, NULL, NULL, NULL, NULL,
NULL, NULL, NULL, NULL, NULL,
NULL, NULL, NULL, NULL, NULL,
NULL, NULL, NULL, NULL, NULL,
NULL, NULL, NULL, NULL, NULL,
NULL, NULL, NULL, NULL, NULL,
NULL, NULL, NULL, NULL, NULL,
NULL, NULL, NULL, NULL, NULL,
NULL, NULL, NULL, NULL, NULL,
NULL, NULL, NULL, NULL, NULL,
NULL, NULL, NULL, NULL, NULL,
NULL, NULL, NULL, NULL, NULL,
NULL, NULL, NULL, NULL, NULL,
NULL, NULL, NULL, NULL, NULL,
NULL, NULL, NULL, NULL, NULL,
NULL, NULL, NULL, NULL, NULL,
NULL, NULL, NULL, NULL, NULL,
NULL, NULL, NULL, NULL, NULL,
-1, TO_DATE('03/21/2018 11:46:37', 'MM/DD/YYYY HH24:MI:SS'), 1013497, TO_DATE('03/21/2018 11:48:05', 'MM/DD/YYYY HH24:MI:SS'), 5614889,
NULL, NULL, 204, 'N', NULL,
NULL, NULL, NULL, NULL, NULL,
NULL, NULL, NULL, NULL, NULL,
NULL, NULL, NULL, NULL, NULL,
NULL, NULL, NULL, NULL, NULL,
NULL, NULL, NULL, NULL, NULL,
NULL, NULL, NULL, NULL, NULL,
NULL, NULL, NULL, NULL, NULL,
NULL, NULL, NULL, NULL, NULL,
NULL, NULL, NULL, NULL, NULL,
NULL, NULL, NULL, NULL, NULL,
NULL, NULL, NULL, NULL, NULL,
NULL, NULL, NULL, NULL, NULL,
NULL, NULL, NULL, NULL, NULL,
NULL, NULL, NULL, NULL, NULL,
NULL, NULL, NULL, NULL, NULL,
NULL, NULL, NULL, NULL, NULL,
NULL, NULL, NULL, NULL, NULL,
NULL, NULL, NULL, NULL, NULL,
NULL, NULL, NULL, NULL, NULL,
NULL, NULL, NULL, NULL, NULL,
NULL, NULL, NULL, NULL, NULL,
NULL, NULL);

COMMIT;

Any Queries Mail Me vivekexpertize@gmail.com

site	Google Webmaster My title page contents