Wednesday, 12 February 2014

Multicolumn Subqueries

Posted by Amar Alam at 22:44 Labels: 1 comments
We can compare elements having multiple values with the set of elements. This is called a multicolumn subquery.

CREATE TABLE EMPDEP(EMPNO NUMBER, DEPTNO NUMBER,SALARY NUMBER);

CREATE TABLE EMPCITY(EMPNO NUMBER, DEPTNO NUMBER,CITY VARCHAR2(30));

INSERT INTO EMPDEP VALUES (1,10,1000);

INSERT INTO EMPDEP VALUES (2,20,2000);

INSERT INTO EMPDEP VALUES (3,30,3000);

INSERT INTO EMPCITY VALUES (1,10,'NAGPUR');

INSERT INTO EMPCITY VALUES (2,20,'DELHI');

INSERT INTO EMPCITY VALUES (3,30,'NAGPUR');

SELECT * FROM EMPDEP

EMPNO DEPTNO SALARY
1 10 1000
2 20 2000
3 30 3000

SELECT * FROM EMPCITY

EMPNO DEPTNO CITY
1 10 NAGPUR
2 20 DELHI
3 30 NAGPUR

SELECT E1.EMPNO, E1.DEPTNO,E1.SALARY
FROM EMPDEP E1 WHERE (E1.EMPNO,E1.DEPTNO)
IN (SELECT E2.EMPNO,E2.DEPTNO FROM EMPCITY E2 WHERE CITY = 'NAGPUR')

EMPNO DEPTNO SALARY
1 10 1000
3 30 3000



 Thanks
AmarAlam

Tuesday, 11 February 2014

I Miss You .... Grand Pa..!!!!

Posted by Amar Alam at 02:47 Labels: 0 comments
1940 ~ 02/02/2014. Dear Grandpa, We miss you, and love you! Thing's are different without you here. You were wonderful in every way, and I thank God for giving us such a wonderful man as a Grandpa! Love,

Ur's
AmarAlam

Thursday, 16 January 2014

SHIP SET in Oracle Order Management

Posted by Amar Alam at 04:43 Labels: , 0 comments
Ship Set , Order Lines and Delivery Details in OM

In Oracle Order Management When we ever add Order line in a SHIP_SET , I will add the data into OE_SETS tables as well as update OE_ORDER_LINES_ALL table for ship_set_ID.After Booking event ship_set_id got populated in WSH_DELIVERY_DETAILS Table.

Now if we TRY to remove the line from the SHIP SET , system will update the WSH_DELIVERY_DETAILS table and OE_ORDER_LINES_ALL table and set the ship_set_id column in both the tables to NULL.

BUT please note that once delivery is CLOSED/SHIPPED , system will not let you remove the line from SHIP_SET ( And why we need that , suppose we have say 5 lines in the SHIP SET and these lines assocaited with say 2 deliveries and if 1 of the of the delivery is closed and we want to remove the lines from the ship set the system will not let yoy do that). Only Option possible is DELETE from OE_SETS and UPDATE on WSH_DELIVERY_DETAILS and OE_ORDER_LINES_ALL ( Off course best practices from the Oracle will not allow us to do any DML on base Oracle Apps table).


Ur's
AmarAlam

Tuesday, 3 December 2013

Wedding Query. ....... ........ (SQL Style):

Posted by Amar Alam at 01:41 Labels: 1 comments
HUSBANDS QUERY:
============

CREATE PROCEDURE MyMarriage (
BrideGroom Male (25) ,
Bride Female(20) )
AS
BEGIN

SELECT
Bride FROM india_ Brides
WHERE FatherInLaw = 'Millionaire'
AND Count(Car) > 20 AND HouseStatus ='ThreeStoreyed'
AND BrideEduStatus IN (B.TECH ,BE ,Degree ,MCA ,MiBA) AND Having Brothers= Null
AND Sisters =Null

SELECT Gold ,Cash,Car,BankBalance
FROM FatherInLaw

UPDATE
MyBankAccout
SETMyBal = MyBal + FatherInLaw.BankBalance

UPDATE
MyLocker
SET MyLockerContents = MyLockerContents + FatherInLawGold

INSERT INTO
MyCarShed VALUES('BMW','Rolls Royce')
END;

Then the WIFE writes the below query:
========================

DROP HUSBAND;
Commit;


Ur's
AmarAlam

Tuesday, 26 November 2013

Link Between AR,SLA and GL

Posted by Amar Alam at 06:54 Labels: 1 comments
SELECT b.NAME batch_name,
                 b.description batch_description,
                 h.je_category,
                 h.je_source,
                 h.period_name je_period_name,
                 h.NAME journal_name,
                 h.status journal_status,
                 h.description je_description,
                 l.je_line_num line_number   
FROM gl_je_batches b,
             gl_je_headers h,
             gl_je_lines l,
             gl_code_combinations_kfv glcc,
             gl_import_references gir,
             xla_ae_lines xlal,
             xla_ae_headers xlah,
             xla_events xlae,
             xla.xla_transaction_entities xlate,
             ra_customer_trx_all rct,
WHERE b.je_batch_id = h.je_batch_id
AND h.je_header_id = l.je_header_id
AND xlal.code_combination_id = glcc.code_combination_id
AND l.je_header_id = gir.je_header_id
AND l.je_line_num = gir.je_line_num
AND gir.gl_sl_link_table = xlal.gl_sl_link_table
AND gir.gl_sl_link_id = xlal.gl_sl_link_id
AND xlal.ae_header_id = xlah.ae_header_id
AND xlah.event_id = xlae.event_id
AND xlae.entity_id = xlate.entity_id
AND xlae.application_id = xlate.application_id
AND h.je_source = 'Receivables'
AND h.period_name = '2009-10'
AND rct.trx_number = xlate.transaction_number
AND xlate.transaction_number =':P_TRX_NUMBER


 Ur's
AmarAlam

Link Between AP,SLA and GL

Posted by Amar Alam at 06:51 Labels: 0 comments
SELECT   c.code_combination_id, h.je_header_id, l.ae_header_id, l.ae_line_num,
         te.source_id_int_1, te.application_id, te.entity_id, h.je_source,
         h.je_category, i.gl_date, s.vendor_name, s.segment1 AS supplier_no,
         l.event_class_code AS event_class, i.invoice_id,
         ad.invoice_distribution_id, i.invoice_num AS transaction_number,
         i.invoice_date, INITCAP (jl.description) description,
         jl.accounted_dr AS debit, jl.accounted_cr AS credit,
         NVL (jl.accounted_dr, 0), NVL (jl.accounted_cr, 0) net_amount
    FROM apps.gl_je_headers h,
         apps.gl_je_lines jl,
         apps.gl_code_combinations c,
         apps.gl_import_references r,
         apps.xla_ae_lines al,
         apps.xla_ae_headers ah,
         apps.xla_distribution_links l,
         apps.ap_invoices_all i,
         apps.ap_invoice_distributions_all ad,
         apps.ap_suppliers s,
         apps.xla_events e,
         apps.xla_transaction_entities te
   WHERE ad.accounting_date BETWEEN :startdate AND :enddate
     AND c.code_combination_id = 6429
       AND ad.line_type_lookup_code = ‘item’
     AND jl.je_header_id = h.je_header_id
     AND jl.code_combination_id = c.code_combination_id
     AND al.gl_sl_link_id = r.gl_sl_link_id
     AND al.ae_header_id = ah.ae_header_id
     AND al.application_id = ah.application_id
     AND ah.application_id = e.application_id
     AND ah.event_id = e.event_id
     AND e.application_id = te.application_id(+)
     AND e.entity_id = te.entity_id(+)
     AND r.je_header_id = jl.je_header_id
     AND r.je_line_num = jl.je_line_num
     AND l.ae_header_id = al.ae_header_id
     AND l.ae_line_num = al.ae_line_num
     AND l.applied_to_source_id_num_1 = i.invoice_id
     AND l.source_distribution_id_num_1 = ad.invoice_distribution_id
     AND ad.invoice_id = i.invoice_id
     AND i.vendor_id = s.vendor_id
ORDER BY i.gl_date DESC


 Ur's
AmarAlam

Oracle FNDLOAD Scripts To Migrate AOL Objects

Posted by Amar Alam at 06:43 Labels: , 5 comments

Following are the usage list of FNDLOAD to upload and download various AOL objects.

Concurrent Program
FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/afcpprog.lct XXCUST_CP.ldt PROGRAM APPLICATION_SHORT_NAME="XXCUST" CONCURRENT_PROGRAM_NAME="XXCUST_CP"

FNDLOAD apps/apps O Y UPLOAD $FND_TOP/patch/115/import/afcpprog.lct XXCUST_CP.ldt UPLOAD_MODE=REPLACE CUSTOM_MODE=FORCE

Lookups
FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/aflvmlu.lct XXCUST_LKP.ldt FND_LOOKUP_TYPE APPLICATION_SHORT_NAME="XXCUST" LOOKUP_TYPE="XXCUST_LKP"

FNDLOAD apps/apps O Y UPLOAD $FND_TOP/patch/115/import/aflvmlu.lct  XXCUST_LKP.ldt UPLOAD_MODE=REPLACE CUSTOM_MODE=FORCE

Message
FNDLOAD apps/apps 0 Y DOWNLOAD $FND_TOP/patch/115/import/afmdmsg.lct XXCUST_MESG.ldt FND_NEW_MESSAGES APPLICATION_SHORT_NAME="XXCUST" MESSAGE_NAME="XXCUST_MESG%"

FNDLOAD apps/apps O Y UPLOAD $FND_TOP/patch/115/import/afmdmsg.lct XXCUST_MESG.ldt UPLOAD_MODE=REPLACE CUSTOM_MODE=FORCE

Request Set and Link

FNDLOAD apps/apps 0 Y DOWNLOAD $FND_TOP/patch/115/import/afcprset.lct XXCUST_RS.ldt REQ_SET REQUEST_SET_NAME='XXCUST_RS'

FNDLOAD apps/apps 0 Y DOWNLOAD $FND_TOP/patch/115/import/afcprset.lct XXCUST_LNK.ldt REQ_SET_LINKS REQUEST_SET_NAME='XXCUST_LNK'


FNDLOAD apps/apps O Y UPLOAD $FND_TOP/patch/115/import/afcprset.lct  XXCUST_RS.ldt UPLOAD_MODE=REPLACE CUSTOM_MODE=FORCE

FNDLOAD apps/apps O Y UPLOAD $FND_TOP/patch/115/import/afcprset.lct  XXCUST_LNK.ldt UPLOAD_MODE=REPLACE CUSTOM_MODE=FORCE

Form Function

FNDLOAD apps/apps 0 Y DOWNLOAD $FND_TOP/patch/115/import/afsload.lct XXCUST_FUNC.ldt FUNCTION FUNC_APP_SHORT_NAME='XXCUST' FUNCTION_NAME='XXCUST_FUNC'

FNDLOAD apps/apps 0 Y UPLOAD $FND_TOP/patch/115/import/afsload.lct XXCUST_FUNC.ldt

Profile

FNDLOAD apps/apps 0 Y DOWNLOAD $FND_TOP/patch/115/import/afscprof.lct XXCUST_PROF.ldt PROFILE PROFILE_NAME="XXCUST_PROFILE" APPLICATION_SHORT_NAME="XXCUST"

FNDLOAD apps/apps 0 Y UPLOAD $FND_TOP/patch/115/import/afscprof.lct XXCUST_FUNC.ldt

Menu

FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/afsload.lct XXCUST_MENU.ldt MENU MENU_NAME="XXCUST_MENU"

FNDLOAD apps/apps O Y UPLOAD $FND_TOP/patch/115/import/afsload.lct XXCUST_MENU.ldt

Data Definition

FNDLOAD apps/apps 0 Y DOWNLOAD $XDO_TOP/patch/115/import/xdotmpl.lct XXCUST_DD.ldt XDO_DS_DEFINITIONS APPLICATION_SHORT_NAME=XXCUST DATA_SOURCE_CODE=XXCUST_DS

FNDLOAD apps/apps O Y UPLOAD $FND_TOP/patch/115/import/afsload.lct XXCUST_DD.ldt


Ur's
AmarAlam
Subscribe to: Posts (Atom)